﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class OrdersDA : IOrdersDA
    {
        private string ABNORMAL = "@ABNORMAL";
        private string ACTUAL_SHIP_DATE = "@ACTUAL_SHIP_DATE";
        private string ACTUAL_WEIGHT = "@ACTUAL_WEIGHT";
        private string APPT_STATUS = "@APPT_STATUS";
        private string B_ADDRESS = "@B_ADDRESS";
        private string B_CITY = "@B_CITY";
        private string B_COMPANY = "@B_COMPANY";
        private string B_CONTACT1 = "@B_CONTACT1";
        private string B_CONTACT2 = "@B_CONTACT2";
        private string B_COUNTIES = "@B_COUNTIES";
        private string B_COUNTRY = "@B_COUNTRY";
        private string B_COUNTRY_CODE = "@B_COUNTRY_CODE";
        private string B_EMAIL1 = "@B_EMAIL1";
        private string B_EMAIL2 = "@B_EMAIL2";
        private string B_FAX1 = "@B_FAX1";
        private string B_FAX2 = "@B_FAX2";
        private string B_PHONE1 = "@B_PHONE1";
        private string B_PHONE2 = "@B_PHONE2";
        private string B_STATE = "@B_STATE";
        private string B_STREET = "@B_STREET";
        private string B_VAT = "@B_VAT";
        private string B_ZIP = "@B_ZIP";
        private string BATCH_FLAG = "@BATCH_FLAG";
        private string BILL_TO_ID = "@BILL_TO_ID";
        private string BUYER_PO = "@BUYER_PO";
        private string C_ADDRESS = "@C_ADDRESS";
        private string C_CITY = "@C_CITY";
        private string C_COMPANY = "@C_COMPANY";
        private string C_CONTACT1 = "@C_CONTACT1";
        private string C_CONTACT2 = "@C_CONTACT2";
        private string C_COUNTIES = "@C_COUNTIES";
        private string C_COUNTRY = "@C_COUNTRY";
        private string C_COUNTRY_CODE = "@C_COUNTRY_CODE";
        private string C_EMAIL1 = "@C_EMAIL1";
        private string C_EMAIL2 = "@C_EMAIL2";
        private string C_FAX1 = "@C_FAX1";
        private string C_FAX2 = "@C_FAX2";
        private string C_PHONE1 = "@C_PHONE1";
        private string C_PHONE2 = "@C_PHONE2";
        private string C_PROV = "@C_PROV";
        private string C_STREET = "@C_STREET";
        private string C_VAT = "@C_VAT";
        private string C_ZIP = "@C_ZIP";
        private string CARRIER_ADDRESS = "@CARRIER_ADDRESS";
        private string CARRIER_CITY = "@CARRIER_CITY";
        private string CARRIER_COUNTRY = "@CARRIER_COUNTRY";
        private string CARRIER_COUNTRY_CODE = "@CARRIER_COUNTRY_CODE";
        private string CARRIER_ID = "@CARRIER_ID";
        private string CARRIER_NAME = "@CARRIER_NAME";
        private string CARRIER_PHONE = "@CARRIER_PHONE";
        private string CARRIER_PROV = "@CARRIER_PROV";
        private string CARRIER_ZIP = "@CARRIER_ZIP";
        private string CONSIGNEE_ID = "@CONSIGNEE_ID";
        private string CONTAINER_QTY = "@CONTAINER_QTY";
        private string CONTAINER_TYPE = "@CONTAINER_TYPE";
        private string CORE_RELEASE_ID = "@CORE_RELEASE_ID";
        private string COUNTRY_DESTINATION = "@COUNTRY_DESTINATION";
        private string COUNTRY_OF_ORIGIN = "@COUNTRY_OF_ORIGIN";
        private string CREATED_BY = "@CREATED_BY";
        private string DECLARATIONS_TYPE = "@DECLARATIONS_TYPE";
        private string DELIVERY_DATE = "@DELIVERY_DATE";
        private string DELIVERY_DATE2 = "@DELIVERY_DATE2";
        private string DELIVERY_PLACE = "@DELIVERY_PLACE";
        private string DEPARTURE_DATE = "@DEPARTURE_DATE";
        private string DEPARTURE_PORT = "@DEPARTURE_PORT";
        private string DESTINATION_PORT = "@DESTINATION_PORT";
        private string DISCHARGE_PLACE = "@DISCHARGE_PLACE";
        private string DOOR = "@DOOR";
        private string DRIVER_NAME = "@DRIVER_NAME";
        private string ENABLE_PACKING = "@ENABLE_PACKING";
        private string EXTERN_ORDER_ID = "@EXTERN_ORDER_ID";
        private string EXTERNAL_LOAD_ID = "@EXTERNAL_LOAD_ID";
        private string EXTERNAL_ORDER_ID2 = "@EXTERNAL_ORDER_ID2";
        private string HANDLING = "@HANDLING";
        private string HANDLING_TYPE = "@HANDLING_TYPE";
        private string INCO_TERMS = "@INCO_TERMS";
        private string INTRANSIT_ID = "@INTRANSIT_ID";
        private string IS_ACTIVE = "@IS_ACTIVE";
        private string IS_CANCEL = "@IS_CANCEL";
        private string IS_COD = "@IS_COD";
        private string IS_DECLARATION = "@IS_DECLARATION";
        private string IS_INVOICE_PRINT = "@IS_INVOICE_PRINT";
        private string IS_PALLET_AUDIT = "@IS_PALLET_AUDIT";
        private string LOAD_ID = "@LOAD_ID";
        private string OPEN_QTY = "@OPEN_QTY";
        private string OPERATING_DATE = "@OPERATING_DATE";
        private string OPERATOR = "@OPERATOR";
        private string ORDER_BREAK = "@ORDER_BREAK";
        private string ORDER_DATE = "@ORDER_DATE";
        private string ORDER_GROUP = "@ORDER_GROUP";
        private string ORDER_ID = "@ORDER_ID";
        private string ORDER_VALUE = "@ORDER_VALUE";
        private string OWNER_ID = "@OWNER_ID";
        private string PACKING_LOCATION = "@PACKING_LOCATION";
        private string PACKING_TYPE = "@PACKING_TYPE";
        private string PAYMENT_TERM = "@PAYMENT_TERM";
        private string PRIORITY = "@PRIORITY";
        private string PRO_NUMBER = "@PRO_NUMBER";
        private string RECEIPT_ID = "@RECEIPT_ID";
        private string REFERENCE_NUM = "@REFERENCE_NUM";
        private string REMARK = "@REMARK";
        private string REMARK2 = "@REMARK2";
        private string REQUESTED_SHIP_DATE = "@REQUESTED_SHIP_DATE";
        private string ROUTE = "@ROUTE";
        private string SHIP_NAME = "@SHIP_NAME";
        private string SHIP_TOGETHER = "@SHIP_TOGETHER";
        private string SORTATION_LOCATION = "@SORTATION_LOCATION";
        private string SPLIT_ORDERS = "@SPLIT_ORDERS";
        private string SQL_CHECK_ORDERS_ID_UNIQUE = " SELECT COUNT(1) FROM @_@ORDERS WHERE ORDER_ID = @ORDER_ID ";
        private string SQL_DELETE_ORDERS = " DELETE FROM @_@ORDERS WHERE ORDER_ID = @ORDER_ID ";
        private string SQL_GET_ORDER_DETAIL_STATUS_FOR_ORDER_STATUS = " SELECT [STATUS] FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID ";
        private string SQL_GET_PO_PRICE_FOR_SHIPPING = "SELECT DISTINCT IL.UNIT_PRICE,RD.RECEIPT_ID,RD.RECEIPT_LINE_ID,RD.PO_ID,RD.PO_LINE_ID  FROM @_@INV_LOT IL LEFT JOIN @_@LOT_ATTRIBUTE LA ON LA.LOT = IL.LOT  LEFT JOIN @_@RECEIPT_DETAIL RD ON RD.RECEIPT_ID = LA.LOT_ATTR10 AND RD.OWNER_ID = LA.OWNER_ID AND RD.SKU_ID = LA.SKU_ID WHERE IL.LOT = @LOT";
        private string SQL_INSERT_ORDERS = " INSERT INTO @_@ORDERS ( WH_ID, ORDER_ID, OWNER_ID, ORDER_DATE, REQUESTED_SHIP_DATE, ACTUAL_SHIP_DATE, DELIVERY_DATE, DELIVERY_DATE2, PRIORITY, TYPE, ORDER_GROUP, OPEN_QTY, STATUS, EXTERN_ORDER_ID, EXTERNAL_ORDER_ID2, REFERENCE_NUM, TRADING_PARTNER, RECEIPT_ID, CONSIGNEE_ID, C_COMPANY, C_ADDRESS, C_CITY, C_PROV, C_ZIP, C_COUNTRY_CODE, C_COUNTRY, C_VAT, C_CONTACT1, C_CONTACT2, C_EMAIL1, C_EMAIL2, C_PHONE1, C_PHONE2, C_FAX1, C_FAX2, BILL_TO_ID, B_COMPANY, B_ADDRESS, B_CITY, B_STATE, B_ZIP, B_COUNTRY_CODE, B_COUNTRY, B_CONTACT1, B_CONTACT2, B_EMAIL1, B_EMAIL2, B_PHONE1, B_PHONE2, B_FAX1, B_FAX2, B_VAT, CARRIER_ID, CARRIER_NAME, CARRIER_ADDRESS, CARRIER_CITY, CARRIER_PROV, CARRIER_ZIP, CARRIER_COUNTRY_CODE, CARRIER_COUNTRY, CARRIER_PHONE, DEPARTURE_DATE, TRAILER_NUMBER, TRAILER_OWNER, DRIVER_NAME, PRO_NUMBER, DOOR, ROUTE, STOP, STAGE, TRANSPORTATION_MODE, TRANSPORTATION_SERVICE, INCO_TERMS, PAYMENT_TERM, CONTAINER_TYPE, CONTAINER_QTY, LOAD_ID, EXTERNAL_LOAD_ID, COUNTRY_OF_ORIGIN, COUNTRY_DESTINATION, DISCHARGE_PLACE, DELIVERY_PLACE, HANDLING_TYPE, BATCH_FLAG, SHIP_TOGETHER, SORTATION_LOCATION, ORDER_BREAK, ENABLE_PACKING, PACKING_LOCATION, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, BUYER_PO, ORDER_VALUE, INTRANSIT_ID, UPDATE_SOURCE, SPLIT_ORDERS, APPT_STATUS, REMARK, REMARK2, IS_ACTIVE, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE,CORE_RELEASE_ID,IS_DECLARATION,PACKING_TYPE,WAYBILL_NO,ACTUAL_WEIGHT,C_COUNTIES,SHIP_NAME,VOYAGE,DESTINATION_PORT,DEPARTURE_PORT,B_COUNTIES,C_STREET,B_STREET,IS_COD,IS_INVOICE_PRINT,OPERATOR,OPERATING_DATE,ABNORMAL,HANDLING,DECLARATIONS_TYPE,WORK_ORDER,TRANSSHIPMENT_PORT ) VALUES ( @WH_ID, @ORDER_ID, @OWNER_ID, @ORDER_DATE, @REQUESTED_SHIP_DATE, @ACTUAL_SHIP_DATE, @DELIVERY_DATE, @DELIVERY_DATE2, @PRIORITY, @TYPE, @ORDER_GROUP, @OPEN_QTY, @STATUS, @EXTERN_ORDER_ID, @EXTERNAL_ORDER_ID2, @REFERENCE_NUM, @TRADING_PARTNER, @RECEIPT_ID, @CONSIGNEE_ID, @C_COMPANY, @C_ADDRESS, @C_CITY, @C_PROV, @C_ZIP, @C_COUNTRY_CODE, @C_COUNTRY, @C_VAT, @C_CONTACT1, @C_CONTACT2, @C_EMAIL1, @C_EMAIL2, @C_PHONE1, @C_PHONE2, @C_FAX1, @C_FAX2, @BILL_TO_ID, @B_COMPANY, @B_ADDRESS, @B_CITY, @B_STATE, @B_ZIP, @B_COUNTRY_CODE, @B_COUNTRY, @B_CONTACT1, @B_CONTACT2, @B_EMAIL1, @B_EMAIL2, @B_PHONE1, @B_PHONE2, @B_FAX1, @B_FAX2, @B_VAT, @CARRIER_ID, @CARRIER_NAME, @CARRIER_ADDRESS, @CARRIER_CITY, @CARRIER_PROV, @CARRIER_ZIP, @CARRIER_COUNTRY_CODE, @CARRIER_COUNTRY, @CARRIER_PHONE, @DEPARTURE_DATE, @TRAILER_NUMBER, @TRAILER_OWNER, @DRIVER_NAME, @PRO_NUMBER, @DOOR, @ROUTE, @STOP, @STAGE, @TRANSPORTATION_MODE, @TRANSPORTATION_SERVICE, @INCO_TERMS, @PAYMENT_TERM, @CONTAINER_TYPE, @CONTAINER_QTY, @LOAD_ID, @EXTERNAL_LOAD_ID, @COUNTRY_OF_ORIGIN, @COUNTRY_DESTINATION, @DISCHARGE_PLACE, @DELIVERY_PLACE, @HANDLING_TYPE, @BATCH_FLAG, @SHIP_TOGETHER, @SORTATION_LOCATION, @ORDER_BREAK, @ENABLE_PACKING, @PACKING_LOCATION, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @BUYER_PO, @ORDER_VALUE, @INTRANSIT_ID, @UPDATE_SOURCE, @SPLIT_ORDERS, @APPT_STATUS, @REMARK, @REMARK2, @IS_ACTIVE, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE(),@CORE_RELEASE_ID,@IS_DECLARATION,@PACKING_TYPE,@WAYBILL_NO,@ACTUAL_WEIGHT,@C_COUNTIES,@SHIP_NAME,@VOYAGE,@DESTINATION_PORT,@DEPARTURE_PORT,@B_COUNTIES,@C_STREET,@B_STREET,@IS_COD,@IS_INVOICE_PRINT,@OPERATOR,@OPERATING_DATE,@ABNORMAL,@HANDLING,@DECLARATIONS_TYPE,@WORK_ORDER,@TRANSSHIPMENT_PORT )  ";
        private string SQL_SELECT_ALL_ORDERS = " SELECT  O.ROWID AS ROWID, O.STATUS AS OrderStatus ,O.WH_ID AS WH_ID, O.ORDER_ID AS ORDER_ID, O.OWNER_ID AS OWNER_ID, O.ORDER_DATE AS ORDER_DATE, O.REQUESTED_SHIP_DATE AS REQUESTED_SHIP_DATE,  O.ACTUAL_SHIP_DATE AS ACTUAL_SHIP_DATE, O.DELIVERY_DATE AS DELIVERY_DATE, O.DELIVERY_DATE2 AS DELIVERY_DATE2, O.PRIORITY AS PRIORITY, T2.[TEXT] AS TYPE_D, O.TYPE AS TYPE,  O.ORDER_GROUP AS ORDER_GROUP, O.OPEN_QTY AS OPEN_QTY, T.[TEXT] AS STATUS_D, O.STATUS AS STATUS, O.EXTERN_ORDER_ID AS EXTERN_ORDER_ID, O.EXTERNAL_ORDER_ID2 AS EXTERNAL_ORDER_ID2, O.REFERENCE_NUM AS REFERENCE_NUM, O.TRADING_PARTNER AS TRADING_PARTNER, O.RECEIPT_ID AS RECEIPT_ID, O.CONSIGNEE_ID AS CONSIGNEE_ID, O.C_COMPANY AS C_COMPANY, O.C_ADDRESS AS C_ADDRESS,  O.C_CITY AS C_CITY, O.C_PROV AS C_PROV, O.C_ZIP AS C_ZIP, O.C_COUNTRY_CODE AS C_COUNTRY_CODE, O.C_COUNTRY AS C_COUNTRY, O.C_VAT AS C_VAT, O.C_CONTACT1 AS C_CONTACT1, O.C_CONTACT2 AS C_CONTACT2, O.C_EMAIL1 AS C_EMAIL1, O.C_EMAIL2 AS C_EMAIL2, O.C_PHONE1 AS C_PHONE1, O.C_PHONE2 AS C_PHONE2, O.C_FAX1 AS C_FAX1, O.C_FAX2 AS C_FAX2, O.BILL_TO_ID AS BILL_TO_ID, O.B_COMPANY AS B_COMPANY, O.B_ADDRESS AS B_ADDRESS, O.B_CITY AS B_CITY, O.B_STATE AS B_STATE, O.B_ZIP AS B_ZIP, O.B_COUNTRY_CODE AS B_COUNTRY_CODE, O.B_COUNTRY AS B_COUNTRY, O.B_CONTACT1 AS B_CONTACT1, O.B_CONTACT2 AS B_CONTACT2, O.B_EMAIL1 AS B_EMAIL1, O.B_EMAIL2 AS B_EMAIL2, O.B_PHONE1 AS B_PHONE1, O.B_PHONE2 AS B_PHONE2,  O.B_FAX1 AS B_FAX1, O.B_FAX2 AS B_FAX2, O.B_VAT AS B_VAT, O.CARRIER_ID AS CARRIER_ID, O.CARRIER_NAME AS CARRIER_NAME, O.CARRIER_ADDRESS AS CARRIER_ADDRESS, O.CARRIER_CITY AS CARRIER_CITY, O.CARRIER_PROV AS CARRIER_PROV, O.CARRIER_ZIP AS CARRIER_ZIP, O.CARRIER_COUNTRY_CODE AS CARRIER_COUNTRY_CODE, O.CARRIER_COUNTRY AS CARRIER_COUNTRY, O.CARRIER_PHONE AS CARRIER_PHONE, O.DEPARTURE_DATE AS DEPARTURE_DATE, O.TRAILER_NUMBER AS TRAILER_NUMBER, O.TRAILER_OWNER AS TRAILER_OWNER, O.DRIVER_NAME AS DRIVER_NAME, O.PRO_NUMBER AS PRO_NUMBER, O.DOOR AS DOOR, O.ROUTE AS ROUTE, O.STOP AS STOP, O.STAGE AS STAGE,  O.TRANSPORTATION_MODE AS TRANSPORTATION_MODE, O.TRANSPORTATION_SERVICE AS TRANSPORTATION_SERVICE, O.INCO_TERMS AS INCO_TERMS, O.PAYMENT_TERM AS PAYMENT_TERM, O.CONTAINER_TYPE AS CONTAINER_TYPE, O.CONTAINER_QTY AS CONTAINER_QTY,  O.LOAD_ID AS LOAD_ID, O.EXTERNAL_LOAD_ID AS EXTERNAL_LOAD_ID, O.COUNTRY_OF_ORIGIN AS COUNTRY_OF_ORIGIN, O.COUNTRY_DESTINATION AS COUNTRY_DESTINATION, O.DISCHARGE_PLACE AS DISCHARGE_PLACE, O.DELIVERY_PLACE AS DELIVERY_PLACE,  O.HANDLING_TYPE AS HANDLING_TYPE, O.BATCH_FLAG AS BATCH_FLAG, O.SHIP_TOGETHER AS SHIP_TOGETHER, O.SORTATION_LOCATION AS SORTATION_LOCATION, O.ORDER_BREAK AS ORDER_BREAK, O.ENABLE_PACKING AS ENABLE_PACKING,  O.PACKING_LOCATION AS PACKING_LOCATION, O.UDF1 AS UDF1, O.UDF2 AS UDF2, O.UDF3 AS UDF3, O.UDF4 AS UDF4, O.UDF5 AS UDF5, O.UDF6 AS UDF6, O.UDF7 AS UDF7, O.UDF8 AS UDF8, O.UDF9 AS UDF9, O.UDF10 AS UDF10, O.BUYER_PO AS BUYER_PO, O.ORDER_VALUE AS ORDER_VALUE, O.INTRANSIT_ID AS INTRANSIT_ID, O.UPDATE_SOURCE AS UPDATE_SOURCE, O.SPLIT_ORDERS AS SPLIT_ORDERS, O.APPT_STATUS AS APPT_STATUS, O.REMARK AS REMARK, O.REMARK2 AS REMARK2, O.IS_ACTIVE AS IS_ACTIVE, O.TS AS TS, O.CREATED_BY AS CREATED_BY, O.CREATED_DATE AS CREATED_DATE,  O.UPDATED_BY AS UPDATED_BY, O.UPDATED_DATE AS UPDATED_DATE,I.DEST_WH_DB_ID  AS DEST_WH_DB_ID,WD.WAVE_ID,O.CORE_RELEASE_ID,O.IS_DECLARATION,O.C_COUNTIES,O.SHIP_NAME,O.VOYAGE,O.DESTINATION_PORT,O.DEPARTURE_PORT,O.B_COUNTIES,O.C_STREET,O.B_STREET,O.IS_COD,O.IS_INVOICE_PRINT,O.OPERATOR,O.OPERATING_DATE,O.ABNORMAL,O.HANDLING,O.DECLARATIONS_TYPE,O.WORK_ORDER,   C.UDF1 AS C_UDF1,O.TRANSSHIPMENT_PORT,O.IS_PALLET_AUDIT AS IS_PALLET_AUDIT  FROM  @_@ORDERS O LEFT JOIN @_@CUSTOMER C ON C.CUSTOMER_ID = O.CONSIGNEE_ID  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = O.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = O.TYPE AND SCD2.SYS_CODE_TYPE = 'OrderType' LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+O.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'OrderType.'+O.TYPE = T2.ITEM_ID  \t\t\tAND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE LEFT JOIN @_@INTRANSIT I ON O.INTRANSIT_ID = I.INTRANSIT_ID  INNER JOIN SYSADM.USER_OWNER UO ON O.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  LEFT JOIN  @_@WAVE_DETAIL WD  ON O.ORDER_ID=WD.ORDER_ID";
        private string SQL_SELECT_ALL_ORDERS_GROUP_BY = " SELECT  O.ROWID AS ROWID, O.STATUS AS OrderStatus ,O.WH_ID AS WH_ID, O.ORDER_ID AS ORDER_ID, O.OWNER_ID AS OWNER_ID, O.ORDER_DATE AS ORDER_DATE, O.REQUESTED_SHIP_DATE AS REQUESTED_SHIP_DATE,  O.ACTUAL_SHIP_DATE AS ACTUAL_SHIP_DATE, O.DELIVERY_DATE AS DELIVERY_DATE, O.DELIVERY_DATE2 AS DELIVERY_DATE2, O.PRIORITY AS PRIORITY, T2.[TEXT] AS TYPE_D, O.TYPE AS TYPE,  O.ORDER_GROUP AS ORDER_GROUP, O.OPEN_QTY AS OPEN_QTY, T.[TEXT] AS STATUS_D, O.STATUS AS STATUS, O.EXTERN_ORDER_ID AS EXTERN_ORDER_ID, O.EXTERNAL_ORDER_ID2 AS EXTERNAL_ORDER_ID2, O.REFERENCE_NUM AS REFERENCE_NUM, O.TRADING_PARTNER AS TRADING_PARTNER, O.RECEIPT_ID AS RECEIPT_ID, O.CONSIGNEE_ID AS CONSIGNEE_ID, O.C_COMPANY AS C_COMPANY, O.C_ADDRESS AS C_ADDRESS,  O.C_CITY AS C_CITY, O.C_PROV AS C_PROV, O.C_ZIP AS C_ZIP, O.C_COUNTRY_CODE AS C_COUNTRY_CODE, O.C_COUNTRY AS C_COUNTRY, O.C_VAT AS C_VAT, O.C_CONTACT1 AS C_CONTACT1, O.C_CONTACT2 AS C_CONTACT2, O.C_EMAIL1 AS C_EMAIL1, O.C_EMAIL2 AS C_EMAIL2, O.C_PHONE1 AS C_PHONE1, O.C_PHONE2 AS C_PHONE2, O.C_FAX1 AS C_FAX1, O.C_FAX2 AS C_FAX2, O.BILL_TO_ID AS BILL_TO_ID, O.B_COMPANY AS B_COMPANY, O.B_ADDRESS AS B_ADDRESS, O.B_CITY AS B_CITY, O.B_STATE AS B_STATE, O.B_ZIP AS B_ZIP, O.B_COUNTRY_CODE AS B_COUNTRY_CODE, O.B_COUNTRY AS B_COUNTRY, O.B_CONTACT1 AS B_CONTACT1, O.B_CONTACT2 AS B_CONTACT2, O.B_EMAIL1 AS B_EMAIL1, O.B_EMAIL2 AS B_EMAIL2, O.B_PHONE1 AS B_PHONE1, O.B_PHONE2 AS B_PHONE2,  O.B_FAX1 AS B_FAX1, O.B_FAX2 AS B_FAX2, O.B_VAT AS B_VAT, O.CARRIER_ID AS CARRIER_ID, O.CARRIER_NAME AS CARRIER_NAME, O.CARRIER_ADDRESS AS CARRIER_ADDRESS, O.CARRIER_CITY AS CARRIER_CITY, O.CARRIER_PROV AS CARRIER_PROV, O.CARRIER_ZIP AS CARRIER_ZIP, O.CARRIER_COUNTRY_CODE AS CARRIER_COUNTRY_CODE, O.CARRIER_COUNTRY AS CARRIER_COUNTRY, O.CARRIER_PHONE AS CARRIER_PHONE, O.DEPARTURE_DATE AS DEPARTURE_DATE, O.TRAILER_NUMBER AS TRAILER_NUMBER, O.TRAILER_OWNER AS TRAILER_OWNER, O.DRIVER_NAME AS DRIVER_NAME, O.PRO_NUMBER AS PRO_NUMBER, O.DOOR AS DOOR, O.ROUTE AS ROUTE, O.STOP AS STOP, O.STAGE AS STAGE,  O.TRANSPORTATION_MODE AS TRANSPORTATION_MODE, O.TRANSPORTATION_SERVICE AS TRANSPORTATION_SERVICE, O.INCO_TERMS AS INCO_TERMS, O.PAYMENT_TERM AS PAYMENT_TERM, O.CONTAINER_TYPE AS CONTAINER_TYPE, O.CONTAINER_QTY AS CONTAINER_QTY,  O.LOAD_ID AS LOAD_ID, O.EXTERNAL_LOAD_ID AS EXTERNAL_LOAD_ID, O.COUNTRY_OF_ORIGIN AS COUNTRY_OF_ORIGIN, O.COUNTRY_DESTINATION AS COUNTRY_DESTINATION, O.DISCHARGE_PLACE AS DISCHARGE_PLACE, O.DELIVERY_PLACE AS DELIVERY_PLACE,  O.HANDLING_TYPE AS HANDLING_TYPE, O.BATCH_FLAG AS BATCH_FLAG, O.SHIP_TOGETHER AS SHIP_TOGETHER, O.SORTATION_LOCATION AS SORTATION_LOCATION, O.ORDER_BREAK AS ORDER_BREAK, O.ENABLE_PACKING AS ENABLE_PACKING,  O.PACKING_LOCATION AS PACKING_LOCATION, O.UDF1 AS UDF1, O.UDF2 AS UDF2, O.UDF3 AS UDF3, O.UDF4 AS UDF4, O.UDF5 AS UDF5, O.UDF6 AS UDF6, O.UDF7 AS UDF7, O.UDF8 AS UDF8, O.UDF9 AS UDF9, O.UDF10 AS UDF10, O.BUYER_PO AS BUYER_PO, O.ORDER_VALUE AS ORDER_VALUE, O.INTRANSIT_ID AS INTRANSIT_ID, O.UPDATE_SOURCE AS UPDATE_SOURCE, O.SPLIT_ORDERS AS SPLIT_ORDERS, O.APPT_STATUS AS APPT_STATUS, O.REMARK AS REMARK, O.REMARK2 AS REMARK2, O.IS_ACTIVE AS IS_ACTIVE, O.TS AS TS, O.CREATED_BY AS CREATED_BY, O.CREATED_DATE AS CREATED_DATE,  O.UPDATED_BY AS UPDATED_BY, O.UPDATED_DATE AS UPDATED_DATE,I.DEST_WH_DB_ID  AS DEST_WH_DB_ID,WD.WAVE_ID,SUM(OD.ORIGINAL_QTY) AS TOTAL_ORIGINAL_QTY,O.CORE_RELEASE_ID,O.IS_DECLARATION, O.C_COUNTIES,O.SHIP_NAME,O.VOYAGE,O.DESTINATION_PORT,O.DEPARTURE_PORT,O.B_COUNTIES,O.C_STREET,O.B_STREET,O.IS_COD,O.IS_INVOICE_PRINT,O.OPERATOR,O.OPERATING_DATE,O.ABNORMAL,O.HANDLING, O.DECLARATIONS_TYPE,O.WORK_ORDER,O.TRANSSHIPMENT_PORT,O.IS_PALLET_AUDIT AS IS_PALLET_AUDIT  FROM  @_@ORDERS O LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = O.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = O.TYPE AND SCD2.SYS_CODE_TYPE = 'OrderType' LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+O.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'OrderType.'+O.TYPE = T2.ITEM_ID  \t\t\tAND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE LEFT JOIN @_@INTRANSIT I ON O.INTRANSIT_ID = I.INTRANSIT_ID  INNER JOIN SYSADM.USER_OWNER UO ON O.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  LEFT JOIN  @_@WAVE_DETAIL WD  ON O.ORDER_ID=WD.ORDER_ID LEFT JOIN  @_@ORDER_DETAIL OD  ON O.ORDER_ID=OD.ORDER_ID";
        private string SQL_SELECT_ORDER_LINE_ID_BY_ORDER_ID = " SELECT LINE_ID FROM @_@ORDER_DETAIL WHERE ORDER_ID = @ORDER_ID ";
        private string SQL_SELECT_ORDERS_WAVE_TEMPLATE = " SELECT @MAX_ORDERS O.ROWID AS ROWID, O.STATUS AS OrderStatus ,O.WH_ID AS WH_ID, O.ORDER_ID AS ORDER_ID, O.OWNER_ID AS OWNER_ID, O.ORDER_DATE AS ORDER_DATE, O.REQUESTED_SHIP_DATE AS REQUESTED_SHIP_DATE,  O.ACTUAL_SHIP_DATE AS ACTUAL_SHIP_DATE, O.DELIVERY_DATE AS DELIVERY_DATE, O.DELIVERY_DATE2 AS DELIVERY_DATE2, O.PRIORITY AS PRIORITY, T2.[TEXT] AS TYPE_D, O.TYPE AS TYPE,  O.ORDER_GROUP AS ORDER_GROUP, O.OPEN_QTY AS OPEN_QTY, T.[TEXT] AS STATUS_D, O.STATUS AS STATUS, O.EXTERN_ORDER_ID AS EXTERN_ORDER_ID, O.EXTERNAL_ORDER_ID2 AS EXTERNAL_ORDER_ID2, O.REFERENCE_NUM AS REFERENCE_NUM, O.TRADING_PARTNER AS TRADING_PARTNER, O.RECEIPT_ID AS RECEIPT_ID, O.CONSIGNEE_ID AS CONSIGNEE_ID, O.C_COMPANY AS C_COMPANY, O.C_ADDRESS AS C_ADDRESS,  O.C_CITY AS C_CITY, O.C_PROV AS C_PROV, O.C_ZIP AS C_ZIP, O.C_COUNTRY_CODE AS C_COUNTRY_CODE, O.C_COUNTRY AS C_COUNTRY, O.C_VAT AS C_VAT, O.C_CONTACT1 AS C_CONTACT1, O.C_CONTACT2 AS C_CONTACT2, O.C_EMAIL1 AS C_EMAIL1, O.C_EMAIL2 AS C_EMAIL2, O.C_PHONE1 AS C_PHONE1, O.C_PHONE2 AS C_PHONE2, O.C_FAX1 AS C_FAX1, O.C_FAX2 AS C_FAX2, O.BILL_TO_ID AS BILL_TO_ID, O.B_COMPANY AS B_COMPANY, O.B_ADDRESS AS B_ADDRESS, O.B_CITY AS B_CITY, O.B_STATE AS B_STATE, O.B_ZIP AS B_ZIP, O.B_COUNTRY_CODE AS B_COUNTRY_CODE, O.B_COUNTRY AS B_COUNTRY, O.B_CONTACT1 AS B_CONTACT1, O.B_CONTACT2 AS B_CONTACT2, O.B_EMAIL1 AS B_EMAIL1, O.B_EMAIL2 AS B_EMAIL2, O.B_PHONE1 AS B_PHONE1, O.B_PHONE2 AS B_PHONE2,  O.B_FAX1 AS B_FAX1, O.B_FAX2 AS B_FAX2, O.B_VAT AS B_VAT, O.CARRIER_ID AS CARRIER_ID, O.CARRIER_NAME AS CARRIER_NAME, O.CARRIER_ADDRESS AS CARRIER_ADDRESS, O.CARRIER_CITY AS CARRIER_CITY, O.CARRIER_PROV AS CARRIER_PROV, O.CARRIER_ZIP AS CARRIER_ZIP, O.CARRIER_COUNTRY_CODE AS CARRIER_COUNTRY_CODE, O.CARRIER_COUNTRY AS CARRIER_COUNTRY, O.CARRIER_PHONE AS CARRIER_PHONE, O.DEPARTURE_DATE AS DEPARTURE_DATE, O.TRAILER_NUMBER AS TRAILER_NUMBER, O.TRAILER_OWNER AS TRAILER_OWNER, O.DRIVER_NAME AS DRIVER_NAME, O.PRO_NUMBER AS PRO_NUMBER, O.DOOR AS DOOR, O.ROUTE AS ROUTE, O.STOP AS STOP, O.STAGE AS STAGE,  O.TRANSPORTATION_MODE AS TRANSPORTATION_MODE, O.TRANSPORTATION_SERVICE AS TRANSPORTATION_SERVICE, O.INCO_TERMS AS INCO_TERMS, O.PAYMENT_TERM AS PAYMENT_TERM, O.CONTAINER_TYPE AS CONTAINER_TYPE, O.CONTAINER_QTY AS CONTAINER_QTY,  O.LOAD_ID AS LOAD_ID, O.EXTERNAL_LOAD_ID AS EXTERNAL_LOAD_ID, O.COUNTRY_OF_ORIGIN AS COUNTRY_OF_ORIGIN, O.COUNTRY_DESTINATION AS COUNTRY_DESTINATION, O.DISCHARGE_PLACE AS DISCHARGE_PLACE, O.DELIVERY_PLACE AS DELIVERY_PLACE,  O.HANDLING_TYPE AS HANDLING_TYPE, O.BATCH_FLAG AS BATCH_FLAG, O.SHIP_TOGETHER AS SHIP_TOGETHER, O.SORTATION_LOCATION AS SORTATION_LOCATION, O.ORDER_BREAK AS ORDER_BREAK, O.ENABLE_PACKING AS ENABLE_PACKING,  O.PACKING_LOCATION AS PACKING_LOCATION, O.UDF1 AS UDF1, O.UDF2 AS UDF2, O.UDF3 AS UDF3, O.UDF4 AS UDF4, O.UDF5 AS UDF5, O.UDF6 AS UDF6, O.UDF7 AS UDF7, O.UDF8 AS UDF8, O.UDF9 AS UDF9, O.UDF10 AS UDF10, O.BUYER_PO AS BUYER_PO, O.ORDER_VALUE AS ORDER_VALUE, O.INTRANSIT_ID AS INTRANSIT_ID, O.UPDATE_SOURCE AS UPDATE_SOURCE, O.SPLIT_ORDERS AS SPLIT_ORDERS, O.APPT_STATUS AS APPT_STATUS, O.REMARK AS REMARK, O.REMARK2 AS REMARK2, O.IS_ACTIVE AS IS_ACTIVE, O.TS AS TS, O.CREATED_BY AS CREATED_BY, O.CREATED_DATE AS CREATED_DATE,  O.UPDATED_BY AS UPDATED_BY, O.UPDATED_DATE AS UPDATED_DATE,I.DEST_WH_DB_ID  AS DEST_WH_DB_ID,WD.WAVE_ID,O.CORE_RELEASE_ID,O.IS_DECLARATION,O.C_COUNTIES,O.SHIP_NAME,O.VOYAGE,O.DESTINATION_PORT,O.DEPARTURE_PORT,O.B_COUNTIES,O.C_STREET,O.B_STREET,O.IS_COD,O.IS_INVOICE_PRINT,O.OPERATOR,O.OPERATING_DATE,O.ABNORMAL,O.HANDLING,ORDERCOUNT.TOTALCOUNT,ORDERSKUCOUNT.TOTALSKUCOUNT, ORDERSKUCOUNT.TOTALQTY,ORDERSKUCOUNT.TOTALCUBE,ORDERSKUCOUNT.TOTALWGT,O.DECLARATIONS_TYPE,O.WORK_ORDER,O.TRANSSHIPMENT_PORT,O.IS_PALLET_AUDIT AS IS_PALLET_AUDIT   FROM  @_@ORDERS O LEFT JOIN (SELECT ORDER_ID,  COUNT(*) AS TOTALCOUNT FROM @_@ORDER_DETAIL GROUP BY  ORDER_ID ) ORDERCOUNT  ON O.ORDER_ID=ORDERCOUNT.ORDER_ID LEFT JOIN (SELECT  ORDER_ID, SUM(S.NET_WGT * OD.ORIGINAL_QTY) AS TOTALWGT, SUM(S.CUBE*OD.ORIGINAL_QTY) AS TOTALCUBE, SUM(OD.ORIGINAL_QTY) AS TOTALQTY,COUNT(DISTINCT ISNULL(OD.OWNER_ID,'') + '' + ISNULL(OD.SKU_ID,'')) AS TOTALSKUCOUNT FROM @_@ORDER_DETAIL OD LEFT JOIN @_@SKU S  ON OD.OWNER_ID = S.OWNER_ID AND OD.SKU_ID = S.SKU_ID   GROUP BY ORDER_ID  )  ORDERSKUCOUNT  ON O.ORDER_ID=ORDERSKUCOUNT.ORDER_ID  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = O.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus' LEFT JOIN @_@SYS_CODE_DETAIL SCD2 ON SCD2.SYS_CODE = O.TYPE AND SCD2.SYS_CODE_TYPE = 'OrderType' LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+O.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN sysadm.TRANSLATION T2 ON 'OrderType.'+O.TYPE = T2.ITEM_ID  \t\t\tAND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE LEFT JOIN @_@INTRANSIT I ON O.INTRANSIT_ID = I.INTRANSIT_ID  INNER JOIN SYSADM.USER_OWNER UO ON O.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID  LEFT JOIN  @_@WAVE_DETAIL WD  ON O.ORDER_ID=WD.ORDER_ID";
        private string SQL_SELECT_SHIPPING_REVIEW_BY_ORDER_ID = " SELECT  O.ORDER_ID,O.OWNER_ID,O.EXTERN_ORDER_ID,O.REFERENCE_NUM,OD.SKU_GROUP_COUNT,OD.TOTALE_QTY,O.STATUS,T.[TEXT] AS STATUS_D FROM @_@ORDERS O LEFT JOIN (SELECT ORDER_ID,SUM(ISNULL(PICKED_QTY,0)) AS TOTALE_QTY, COUNT(*) AS SKU_GROUP_COUNT FROM @_@ORDER_DETAIL GROUP BY ORDER_ID ) AS OD ON OD.ORDER_ID =O.ORDER_ID   LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = O.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus'  LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+O.STATUS = T.ITEM_ID   AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON O.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_SHIPPING_WHIGHT_BY_ORDER_ID = "  SELECT O.ORDER_ID,O.OWNER_ID,O.IS_CANCEL,O.STATUS,T.[TEXT] AS STATUS_D,O.CARRIER_ID,O.WAYBILL_NO,O.ACTUAL_WEIGHT,CASE  WHEN O.PRINT_COUNT>0 THEN  'Y' ELSE 'N' END AS IS_PRINT,  O.PRINT_COUNT,O.REFERENCE_NUM,SUM(S.GROSS_WGT*OD.PICKED_QTY) AS TOTAL_WGT FROM  @_@ORDERS O   LEFT JOIN  @_@ORDER_DETAIL OD ON O.ORDER_ID=OD.ORDER_ID LEFT JOIN  @_@SKU S ON OD.OWNER_ID =S.OWNER_ID AND OD.SKU_ID=S.SKU_ID   LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = O.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus'  LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+O.STATUS = T.ITEM_ID   AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON O.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_UPDATE_ACTUAL_SHIP_DATE_BY_ORDER_ID = " UPDATE @_@ORDERS  SET ACTUAL_SHIP_DATE=@ACTUAL_SHIP_DATE , UPDATED_DATE=@UPDATED_DATE,UPDATED_BY=@UPDATED_BY WHERE ORDER_ID=@ORDER_ID";
        private string SQL_UPDATE_CARRIER_ID_BY_ORDER_ID = " UPDATE @_@ORDERS  SET CARRIER_ID=@CARRIER_ID , UPDATED_DATE=getdate(),UPDATED_BY=@UPDATED_BY WHERE ORDER_ID=@ORDER_ID";
        private string SQL_UPDATE_ORDER_BATCH_FLAG = " UPDATE @_@ORDERS SET BATCH_FLAG = @BATCH_FLAG WHERE ORDER_ID = @ORDER_ID ";
        private string SQL_UPDATE_ORDER_EXTERN_ORDER_ID_BY_ORDER_ID = " UPDATE @_@ORDERS SET EXTERN_ORDER_ID=@EXTERN_ORDER_ID, UPDATED_DATE=GETDATE(),UPDATED_BY=@UPDATED_BY WHERE ORDER_ID=@ORDER_ID ";
        private string SQL_UPDATE_ORDER_TRACKING_BY_ORDER_ID = " UPDATE @_@ORDERS SET OPERATOR=@OPERATOR,    OPERATING_DATE=@OPERATING_DATE,  ABNORMAL=@ABNORMAL,  HANDLING =@HANDLING, STATUS=@STATUS,UPDATED_DATE=GETDATE(),UPDATED_BY=@UPDATED_BY WHERE ORDER_ID=@ORDER_ID";
        private string SQL_UPDATE_ORDERS = " UPDATE @_@ORDERS SET WH_ID = @WH_ID, OWNER_ID = @OWNER_ID, ORDER_DATE = @ORDER_DATE, REQUESTED_SHIP_DATE = @REQUESTED_SHIP_DATE, ACTUAL_SHIP_DATE = @ACTUAL_SHIP_DATE, DELIVERY_DATE = @DELIVERY_DATE, DELIVERY_DATE2 = @DELIVERY_DATE2, PRIORITY = @PRIORITY, TYPE = @TYPE, ORDER_GROUP = @ORDER_GROUP, OPEN_QTY = @OPEN_QTY, STATUS = @STATUS, EXTERN_ORDER_ID = @EXTERN_ORDER_ID, EXTERNAL_ORDER_ID2 = @EXTERNAL_ORDER_ID2, REFERENCE_NUM = @REFERENCE_NUM, TRADING_PARTNER = @TRADING_PARTNER, RECEIPT_ID = @RECEIPT_ID, CONSIGNEE_ID = @CONSIGNEE_ID, C_COMPANY = @C_COMPANY, C_ADDRESS = @C_ADDRESS, C_CITY = @C_CITY, C_PROV = @C_PROV, C_ZIP = @C_ZIP, C_COUNTRY_CODE = @C_COUNTRY_CODE, C_COUNTRY = @C_COUNTRY, C_VAT = @C_VAT, C_CONTACT1 = @C_CONTACT1, C_CONTACT2 = @C_CONTACT2, C_EMAIL1 = @C_EMAIL1, C_EMAIL2 = @C_EMAIL2, C_PHONE1 = @C_PHONE1, C_PHONE2 = @C_PHONE2, C_FAX1 = @C_FAX1, C_FAX2 = @C_FAX2, BILL_TO_ID = @BILL_TO_ID, B_COMPANY = @B_COMPANY, B_ADDRESS = @B_ADDRESS, B_CITY = @B_CITY, B_STATE = @B_STATE, B_ZIP = @B_ZIP, B_COUNTRY_CODE = @B_COUNTRY_CODE, B_COUNTRY = @B_COUNTRY, B_CONTACT1 = @B_CONTACT1, B_CONTACT2 = @B_CONTACT2, B_EMAIL1 = @B_EMAIL1, B_EMAIL2 = @B_EMAIL2, B_PHONE1 = @B_PHONE1, B_PHONE2 = @B_PHONE2, B_FAX1 = @B_FAX1, B_FAX2 = @B_FAX2, B_VAT = @B_VAT, CARRIER_ID = @CARRIER_ID, CARRIER_NAME = @CARRIER_NAME, CARRIER_ADDRESS = @CARRIER_ADDRESS, CARRIER_CITY = @CARRIER_CITY, CARRIER_PROV = @CARRIER_PROV, CARRIER_ZIP = @CARRIER_ZIP, CARRIER_COUNTRY_CODE = @CARRIER_COUNTRY_CODE, CARRIER_COUNTRY = @CARRIER_COUNTRY, CARRIER_PHONE = @CARRIER_PHONE, DEPARTURE_DATE = @DEPARTURE_DATE, TRAILER_NUMBER = @TRAILER_NUMBER, TRAILER_OWNER = @TRAILER_OWNER, DRIVER_NAME = @DRIVER_NAME, PRO_NUMBER = @PRO_NUMBER, DOOR = @DOOR, ROUTE = @ROUTE, STOP = @STOP, STAGE = @STAGE, TRANSPORTATION_MODE = @TRANSPORTATION_MODE, TRANSPORTATION_SERVICE = @TRANSPORTATION_SERVICE, INCO_TERMS = @INCO_TERMS, PAYMENT_TERM = @PAYMENT_TERM, CONTAINER_TYPE = @CONTAINER_TYPE, CONTAINER_QTY = @CONTAINER_QTY, LOAD_ID = @LOAD_ID, EXTERNAL_LOAD_ID = @EXTERNAL_LOAD_ID, COUNTRY_OF_ORIGIN = @COUNTRY_OF_ORIGIN, COUNTRY_DESTINATION = @COUNTRY_DESTINATION, DISCHARGE_PLACE = @DISCHARGE_PLACE, DELIVERY_PLACE = @DELIVERY_PLACE, HANDLING_TYPE = @HANDLING_TYPE, BATCH_FLAG = @BATCH_FLAG, SHIP_TOGETHER = @SHIP_TOGETHER, SORTATION_LOCATION = @SORTATION_LOCATION, ORDER_BREAK = @ORDER_BREAK, ENABLE_PACKING = @ENABLE_PACKING, PACKING_LOCATION = @PACKING_LOCATION, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, BUYER_PO = @BUYER_PO, ORDER_VALUE = @ORDER_VALUE, INTRANSIT_ID = @INTRANSIT_ID, UPDATE_SOURCE = @UPDATE_SOURCE, SPLIT_ORDERS = @SPLIT_ORDERS, APPT_STATUS = @APPT_STATUS, REMARK = @REMARK, REMARK2 = @REMARK2, IS_ACTIVE = @IS_ACTIVE, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE(),CORE_RELEASE_ID=@CORE_RELEASE_ID,IS_DECLARATION=@IS_DECLARATION,PACKING_TYPE=@PACKING_TYPE,WAYBILL_NO=@WAYBILL_NO,ACTUAL_WEIGHT=@ACTUAL_WEIGHT,C_COUNTIES=@C_COUNTIES,SHIP_NAME=@SHIP_NAME,VOYAGE=@VOYAGE,DESTINATION_PORT=@DESTINATION_PORT,DEPARTURE_PORT=@DEPARTURE_PORT,B_COUNTIES=@B_COUNTIES,C_STREET=@C_STREET,B_STREET=@B_STREET,IS_COD=@IS_COD,IS_INVOICE_PRINT=@IS_INVOICE_PRINT,OPERATOR=@OPERATOR,OPERATING_DATE=@OPERATING_DATE,ABNORMAL=@ABNORMAL,HANDLING=@HANDLING,DECLARATIONS_TYPE=@DECLARATIONS_TYPE,WORK_ORDER=@WORK_ORDER,TRANSSHIPMENT_PORT=@TRANSSHIPMENT_PORT  WHERE ORDER_ID = @ORDER_ID  ";
        private string SQL_UPDATE_ORDERS_SHIPPING_WEIGHT = " UPDATE @_@ORDERS SET PACKING_TYPE=@PACKING_TYPE,WAYBILL_NO=@WAYBILL_NO,ACTUAL_WEIGHT=@ACTUAL_WEIGHT, UPDATED_BY = @UPDATED_BY, UPDATED_DATE =GETDATE() WHERE ORDER_ID = @ORDER_ID ";
        private string SQL_UPDATE_ORDERS_STATUS = " UPDATE @_@ORDERS SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE =GETDATE() WHERE ORDER_ID = @ORDER_ID ";
        private string SQL_UPDATE_ORDERS_STATUS_4_SHIP = " UPDATE @_@ORDERS SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, ACTUAL_SHIP_DATE = GETDATE(), UPDATED_DATE =GETDATE() WHERE ORDER_ID = @ORDER_ID ";
        private string STAGE = "@STAGE";
        private string STATUS = "@STATUS";
        private string STOP = "@STOP";
        private string TRADING_PARTNER = "@TRADING_PARTNER";
        private string TRAILER_NUMBER = "@TRAILER_NUMBER";
        private string TRAILER_OWNER = "@TRAILER_OWNER";
        private string TRANSPORTATION_MODE = "@TRANSPORTATION_MODE";
        private string TRANSPORTATION_SERVICE = "@TRANSPORTATION_SERVICE";
        private string TRANSSHIPMENT_PORT = "@TRANSSHIPMENT_PORT";
        private string TS = "@TS";
        private string TYPE = "@TYPE";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UPDATE_SOURCE = "@UPDATE_SOURCE";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VOYAGE = "@VOYAGE";
        private string WAYBILL_NO = "@WAYBILL_NO";
        private string WH_ID = "@WH_ID";
        private string WORK_ORDER = "@WORK_ORDER";

        public bool CheckOrdersIDUnique(string ordersID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", ordersID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_ORDERS_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckOrdersIDUnique(DataBase dataBase, DbTransaction tran, string ordersID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", ordersID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_ORDERS_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteOrders(List<string> ordersID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < ordersID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", ordersID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ORDERS.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteOrders(DataBase dataBase, DbTransaction tran, List<string> ordersID, List<string> whLoginID)
        {
            int result = 0;
            try
            {
                for (int i = 0; i < whLoginID.Count; i++)
                {
                    for (int j = 0; j < ordersID.Count; j++)
                    {
                        SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", ordersID[j]) };
                        result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_ORDERS.Replace("@_@", whLoginID[i]), paras);
                    }
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllOrders(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDERS.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllOrders(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDERS.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        private List<string> GetOrderIDByCoreReleaseID(string coreReleaseID, string whLoginID)
        {
            string sql = "SELECT ORDER_ID FROM @_@ORDERS WHERE CORE_RELEASE_ID = @CORE_RELEASE_ID AND DECLARATIONS_TYPE = 'CR'";
            sql = sql.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CORE_RELEASE_ID", coreReleaseID) };
            List<string> orderIDList = new List<string>();
            DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                orderIDList.Add(ds.Tables[0].Rows[i][0].ToString());
            }
            return orderIDList;
        }

        public List<string> GetOrderLineIDsByOrderID(string orderID, string whLoginID)
        {
            List<string> orderLineIDs = new List<string>();
            string sql = this.SQL_SELECT_ORDER_LINE_ID_BY_ORDER_ID.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    orderLineIDs.Add(reader["LINE_ID"].ToString());
                }
            }
            return orderLineIDs;
        }

        public List<string> GetOrderLineIDsByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string whLoginID)
        {
            List<string> orderLineIDs = new List<string>();
            string sql = this.SQL_SELECT_ORDER_LINE_ID_BY_ORDER_ID.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                orderLineIDs.Add(reader["LINE_ID"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return orderLineIDs;
        }

        public List<OrdersInfo> GetOrderListByReferenceNumAndType(string ReferenceNum, string Type, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDERS.Replace("@_@", whLoginID) + " WHERE O.REFERENCE_NUM = @REFERENCE_NUM AND TYPE = @TYPE ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@REFERENCE_NUM", ReferenceNum), new SqlParameter("@TYPE", Type), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrdersInfo> ordersList = new List<OrdersInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    OrdersInfo ordersInfo = new OrdersInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["DELIVERY_DATE2"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE2"].ToString()) : (CS00002 = new DateTime()), reader["PRIORITY"].ToString(), reader["TYPE"].ToString(), reader["ORDER_GROUP"].ToString(), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERNAL_ORDER_ID2"].ToString(), reader["REFERENCE_NUM"].ToString(), reader["TRADING_PARTNER"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CONSIGNEE_ID"].ToString(), reader["C_COMPANY"].ToString(), reader["C_ADDRESS"].ToString(), reader["C_CITY"].ToString(), reader["C_PROV"].ToString(), reader["C_ZIP"].ToString(), reader["C_COUNTRY_CODE"].ToString(), reader["C_COUNTRY"].ToString(), reader["C_VAT"].ToString(), reader["C_CONTACT1"].ToString(), reader["C_CONTACT2"].ToString(), reader["C_EMAIL1"].ToString(), reader["C_EMAIL2"].ToString(), reader["C_PHONE1"].ToString(), reader["C_PHONE2"].ToString(), reader["C_FAX1"].ToString(), reader["C_FAX2"].ToString(), reader["BILL_TO_ID"].ToString(), reader["B_COMPANY"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_VAT"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY_CODE"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_PHONE"].ToString(), (reader["DEPARTURE_DATE"].ToString() != "") ? DateTime.Parse(reader["DEPARTURE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["DRIVER_NAME"].ToString(), reader["PRO_NUMBER"].ToString(), reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["STAGE"].ToString(), reader["TRANSPORTATION_MODE"].ToString(), reader["TRANSPORTATION_SERVICE"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["LOAD_ID"].ToString(), reader["EXTERNAL_LOAD_ID"].ToString(), reader["COUNTRY_OF_ORIGIN"].ToString(), reader["COUNTRY_DESTINATION"].ToString(), reader["DISCHARGE_PLACE"].ToString(), reader["DELIVERY_PLACE"].ToString(), reader["HANDLING_TYPE"].ToString(), reader["BATCH_FLAG"].ToString(), reader["SHIP_TOGETHER"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["ORDER_BREAK"].ToString(), reader["ENABLE_PACKING"].ToString(), reader["PACKING_LOCATION"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["BUYER_PO"].ToString(), (reader["ORDER_VALUE"].ToString() != "") ? decimal.Parse(reader["ORDER_VALUE"].ToString()) : 0M, reader["INTRANSIT_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["SPLIT_ORDERS"].ToString(), reader["APPT_STATUS"].ToString(), reader["REMARK"].ToString(), reader["REMARK2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["DEST_WH_DB_ID"].ToString(), reader["STATUS_D"].ToString(), reader["TYPE_D"].ToString(), reader["CORE_RELEASE_ID"].ToString(), reader["IS_DECLARATION"].ToString(), reader["C_COUNTIES"].ToString(), reader["SHIP_NAME"].ToString(), reader["VOYAGE"].ToString(), reader["DESTINATION_PORT"].ToString(), reader["DEPARTURE_PORT"].ToString(), reader["TRANSSHIPMENT_PORT"].ToString(), reader["B_COUNTIES"].ToString(), reader["B_STREET"].ToString(), reader["C_STREET"].ToString(), reader["IS_COD"].ToString(), reader["IS_INVOICE_PRINT"].ToString(), reader["OPERATOR"].ToString(), (reader["OPERATING_DATE"].ToString() != "") ? DateTime.Parse(reader["OPERATING_DATE"].ToString()) : (CS00002 = new DateTime()), reader["ABNORMAL"].ToString(), reader["HANDLING"].ToString(), reader["DECLARATIONS_TYPE"].ToString(), reader["WORK_ORDER"].ToString(), reader["IS_PALLET_AUDIT"].ToString());
                    ordersList.Add(ordersInfo);
                }
            }
            return ordersList;
        }

        public List<OrdersInfo> GetOrderListByReferenceNumAndType(DataBase dataBase, DbTransaction tran, string ReferenceNum, string Type, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDERS.Replace("@_@", whLoginID) + " WHERE O.REFERENCE_NUM = @REFERENCE_NUM AND TYPE = @TYPE ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@REFERENCE_NUM", ReferenceNum), new SqlParameter("@TYPE", Type), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrdersInfo> ordersList = new List<OrdersInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                OrdersInfo ordersInfo = new OrdersInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["DELIVERY_DATE2"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE2"].ToString()) : (CS00002 = new DateTime()), reader["PRIORITY"].ToString(), reader["TYPE"].ToString(), reader["ORDER_GROUP"].ToString(), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERNAL_ORDER_ID2"].ToString(), reader["REFERENCE_NUM"].ToString(), reader["TRADING_PARTNER"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CONSIGNEE_ID"].ToString(), reader["C_COMPANY"].ToString(), reader["C_ADDRESS"].ToString(), reader["C_CITY"].ToString(), reader["C_PROV"].ToString(), reader["C_ZIP"].ToString(), reader["C_COUNTRY_CODE"].ToString(), reader["C_COUNTRY"].ToString(), reader["C_VAT"].ToString(), reader["C_CONTACT1"].ToString(), reader["C_CONTACT2"].ToString(), reader["C_EMAIL1"].ToString(), reader["C_EMAIL2"].ToString(), reader["C_PHONE1"].ToString(), reader["C_PHONE2"].ToString(), reader["C_FAX1"].ToString(), reader["C_FAX2"].ToString(), reader["BILL_TO_ID"].ToString(), reader["B_COMPANY"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_VAT"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY_CODE"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_PHONE"].ToString(), (reader["DEPARTURE_DATE"].ToString() != "") ? DateTime.Parse(reader["DEPARTURE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["DRIVER_NAME"].ToString(), reader["PRO_NUMBER"].ToString(), reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["STAGE"].ToString(), reader["TRANSPORTATION_MODE"].ToString(), reader["TRANSPORTATION_SERVICE"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["LOAD_ID"].ToString(), reader["EXTERNAL_LOAD_ID"].ToString(), reader["COUNTRY_OF_ORIGIN"].ToString(), reader["COUNTRY_DESTINATION"].ToString(), reader["DISCHARGE_PLACE"].ToString(), reader["DELIVERY_PLACE"].ToString(), reader["HANDLING_TYPE"].ToString(), reader["BATCH_FLAG"].ToString(), reader["SHIP_TOGETHER"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["ORDER_BREAK"].ToString(), reader["ENABLE_PACKING"].ToString(), reader["PACKING_LOCATION"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["BUYER_PO"].ToString(), (reader["ORDER_VALUE"].ToString() != "") ? decimal.Parse(reader["ORDER_VALUE"].ToString()) : 0M, reader["INTRANSIT_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["SPLIT_ORDERS"].ToString(), reader["APPT_STATUS"].ToString(), reader["REMARK"].ToString(), reader["REMARK2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["DEST_WH_DB_ID"].ToString(), reader["STATUS_D"].ToString(), reader["TYPE_D"].ToString(), reader["CORE_RELEASE_ID"].ToString(), reader["IS_DECLARATION"].ToString(), reader["C_COUNTIES"].ToString(), reader["SHIP_NAME"].ToString(), reader["VOYAGE"].ToString(), reader["DESTINATION_PORT"].ToString(), reader["DEPARTURE_PORT"].ToString(), reader["TRANSSHIPMENT_PORT"].ToString(), reader["B_COUNTIES"].ToString(), reader["B_STREET"].ToString(), reader["C_STREET"].ToString(), reader["IS_COD"].ToString(), reader["IS_INVOICE_PRINT"].ToString(), reader["OPERATOR"].ToString(), (reader["OPERATING_DATE"].ToString() != "") ? DateTime.Parse(reader["OPERATING_DATE"].ToString()) : (CS00002 = new DateTime()), reader["ABNORMAL"].ToString(), reader["HANDLING"].ToString(), reader["DECLARATIONS_TYPE"].ToString(), reader["WORK_ORDER"].ToString(), reader["IS_PALLET_AUDIT"].ToString());
                ordersList.Add(ordersInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return ordersList;
        }

        public List<OrdersInfo> GetOrdersByCRID(string CRID, string ownerID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDERS_GROUP_BY.Replace("@_@", whLoginID) + " WHERE O.CORE_RELEASE_ID = @CORE_RELEASE_ID AND O.OWNER_ID = @OWNER_ID  AND (O.STATUS ='Shipped' OR O.STATUS='Closed')  AND O.DECLARATIONS_TYPE = 'CR' " + " GROUP BY  O.ROWID , O.STATUS  ,O.WH_ID , O.ORDER_ID , O.OWNER_ID, O.ORDER_DATE , O.REQUESTED_SHIP_DATE,O.ACTUAL_SHIP_DATE , O.DELIVERY_DATE , O.DELIVERY_DATE2 , O.PRIORITY , T2.[TEXT] , O.TYPE ,O.ORDER_GROUP, O.OPEN_QTY , T.[TEXT] , O.STATUS , O.EXTERN_ORDER_ID , O.EXTERNAL_ORDER_ID2 ,O.ORDER_GROUP, O.OPEN_QTY , T.[TEXT] , O.STATUS , O.EXTERN_ORDER_ID , O.EXTERNAL_ORDER_ID2 ,O.REFERENCE_NUM , O.TRADING_PARTNER, O.RECEIPT_ID , O.CONSIGNEE_ID, O.C_COMPANY , O.C_ADDRESS ,O.C_CITY, O.C_PROV , O.C_ZIP, O.C_COUNTRY_CODE , O.C_COUNTRY, O.C_VAT , O.C_CONTACT1 ,O.C_CONTACT2 , O.C_EMAIL1 , O.C_EMAIL2 , O.C_PHONE1 , O.C_PHONE2, O.C_FAX1 , O.C_FAX2 , O.BILL_TO_ID , O.B_COMPANY , O.B_ADDRESS , O.B_CITY , O.B_STATE , O.B_ZIP, O.B_COUNTRY_CODE, O.B_COUNTRY , O.B_CONTACT1 , O.B_CONTACT2, O.B_EMAIL1 , O.B_EMAIL2, O.B_PHONE1 , O.B_PHONE2 ,O.B_FAX1 , O.B_FAX2 , O.B_VAT , O.CARRIER_ID , O.CARRIER_NAME, O.CARRIER_ADDRESS, O.CARRIER_CITY , O.CARRIER_PROV , O.CARRIER_ZIP , O.CARRIER_COUNTRY_CODE , O.CARRIER_COUNTRY , O.CARRIER_PHONE , O.DEPARTURE_DATE ,O.TRAILER_NUMBER , O.TRAILER_OWNER , O.DRIVER_NAME, O.PRO_NUMBER, O.DOOR, O.ROUTE , O.STOP , O.STAGE ,O.TRANSPORTATION_MODE, O.TRANSPORTATION_SERVICE , O.INCO_TERMS , O.PAYMENT_TERM , O.CONTAINER_TYPE , O.CONTAINER_QTY ,O.LOAD_ID , O.EXTERNAL_LOAD_ID , O.COUNTRY_OF_ORIGIN , O.COUNTRY_DESTINATION , O.DISCHARGE_PLACE , O.DELIVERY_PLACE ,O.HANDLING_TYPE , O.BATCH_FLAG, O.SHIP_TOGETHER , O.SORTATION_LOCATION , O.ORDER_BREAK , O.ENABLE_PACKING ,O.PACKING_LOCATION , O.UDF1 , O.UDF2 , O.UDF3, O.UDF4 , O.UDF5, O.UDF6 , O.UDF7, O.UDF8 ,O.UDF9 , O.UDF10 , O.BUYER_PO , O.ORDER_VALUE , O.INTRANSIT_ID , O.UPDATE_SOURCE , O.SPLIT_ORDERS , O.APPT_STATUS, O.REMARK, O.REMARK2 , O.IS_ACTIVE , O.TS, O.CREATED_BY, O.CREATED_DATE,O.UPDATED_BY , O.UPDATED_DATE,I.DEST_WH_DB_ID,WD.WAVE_ID,O.CORE_RELEASE_ID,O.IS_DECLARATION,O.C_COUNTIES,O.SHIP_NAME,O.VOYAGE,O.DESTINATION_PORT,O.DEPARTURE_PORT,O.B_COUNTIES,O.C_STREET,O.B_STREET,O.IS_COD,O.IS_INVOICE_PRINT,O.OPERATOR,O.OPERATING_DATE,O.ABNORMAL,O.HANDLING,O.DECLARATIONS_TYPE,O.WORK_ORDER,O.TRANSSHIPMENT_PORT,O.IS_PALLET_AUDIT ";
            List<OrdersInfo> ordersInfos = new List<OrdersInfo>();
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CORE_RELEASE_ID", CRID), new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            OrdersInfo ordersInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    ordersInfo = new OrdersInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["DELIVERY_DATE2"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE2"].ToString()) : (CS00002 = new DateTime()), reader["PRIORITY"].ToString(), reader["TYPE"].ToString(), reader["ORDER_GROUP"].ToString(), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERNAL_ORDER_ID2"].ToString(), reader["REFERENCE_NUM"].ToString(), reader["TRADING_PARTNER"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CONSIGNEE_ID"].ToString(), reader["C_COMPANY"].ToString(), reader["C_ADDRESS"].ToString(), reader["C_CITY"].ToString(), reader["C_PROV"].ToString(), reader["C_ZIP"].ToString(), reader["C_COUNTRY_CODE"].ToString(), reader["C_COUNTRY"].ToString(), reader["C_VAT"].ToString(), reader["C_CONTACT1"].ToString(), reader["C_CONTACT2"].ToString(), reader["C_EMAIL1"].ToString(), reader["C_EMAIL2"].ToString(), reader["C_PHONE1"].ToString(), reader["C_PHONE2"].ToString(), reader["C_FAX1"].ToString(), reader["C_FAX2"].ToString(), reader["BILL_TO_ID"].ToString(), reader["B_COMPANY"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_VAT"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY_CODE"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_PHONE"].ToString(), (reader["DEPARTURE_DATE"].ToString() != "") ? DateTime.Parse(reader["DEPARTURE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["DRIVER_NAME"].ToString(), reader["PRO_NUMBER"].ToString(), reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["STAGE"].ToString(), reader["TRANSPORTATION_MODE"].ToString(), reader["TRANSPORTATION_SERVICE"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["LOAD_ID"].ToString(), reader["EXTERNAL_LOAD_ID"].ToString(), reader["COUNTRY_OF_ORIGIN"].ToString(), reader["COUNTRY_DESTINATION"].ToString(), reader["DISCHARGE_PLACE"].ToString(), reader["DELIVERY_PLACE"].ToString(), reader["HANDLING_TYPE"].ToString(), reader["BATCH_FLAG"].ToString(), reader["SHIP_TOGETHER"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["ORDER_BREAK"].ToString(), reader["ENABLE_PACKING"].ToString(), reader["PACKING_LOCATION"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["BUYER_PO"].ToString(), (reader["ORDER_VALUE"].ToString() != "") ? decimal.Parse(reader["ORDER_VALUE"].ToString()) : 0M, reader["INTRANSIT_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["SPLIT_ORDERS"].ToString(), reader["APPT_STATUS"].ToString(), reader["REMARK"].ToString(), reader["REMARK2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["DEST_WH_DB_ID"].ToString(), reader["STATUS_D"].ToString(), reader["TYPE_D"].ToString(), reader["CORE_RELEASE_ID"].ToString(), reader["IS_DECLARATION"].ToString(), reader["C_COUNTIES"].ToString(), reader["SHIP_NAME"].ToString(), reader["VOYAGE"].ToString(), reader["DESTINATION_PORT"].ToString(), reader["DEPARTURE_PORT"].ToString(), reader["TRANSSHIPMENT_PORT"].ToString(), reader["B_COUNTIES"].ToString(), reader["B_STREET"].ToString(), reader["C_STREET"].ToString(), reader["IS_COD"].ToString(), reader["IS_INVOICE_PRINT"].ToString(), reader["OPERATOR"].ToString(), (reader["OPERATING_DATE"].ToString() != "") ? DateTime.Parse(reader["OPERATING_DATE"].ToString()) : (CS00002 = new DateTime()), reader["ABNORMAL"].ToString(), reader["HANDLING"].ToString(), reader["DECLARATIONS_TYPE"].ToString(), reader["WORK_ORDER"].ToString(), reader["IS_PALLET_AUDIT"].ToString());
                    ordersInfos.Add(ordersInfo);
                }
            }
            return ordersInfos;
        }

        public List<OrdersInfo> GetOrdersByCRID(DataBase dataBase, DbTransaction tran, string CRID, string ownerID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDERS_GROUP_BY.Replace("@_@", whLoginID) + " WHERE O.CORE_RELEASE_ID = @CORE_RELEASE_ID AND O.OWNER_ID = @OWNER_ID  AND (O.STATUS ='Shipped' OR O.STATUS='Closed')   AND O.DECLARATIONS_TYPE = 'CR' " + " GROUP BY  O.ROWID , O.STATUS  ,O.WH_ID , O.ORDER_ID , O.OWNER_ID, O.ORDER_DATE , O.REQUESTED_SHIP_DATE,O.ACTUAL_SHIP_DATE , O.DELIVERY_DATE , O.DELIVERY_DATE2 , O.PRIORITY , T2.[TEXT] , O.TYPE ,O.ORDER_GROUP, O.OPEN_QTY , T.[TEXT] , O.STATUS , O.EXTERN_ORDER_ID , O.EXTERNAL_ORDER_ID2 ,O.ORDER_GROUP, O.OPEN_QTY , T.[TEXT] , O.STATUS , O.EXTERN_ORDER_ID , O.EXTERNAL_ORDER_ID2 ,O.REFERENCE_NUM , O.TRADING_PARTNER, O.RECEIPT_ID , O.CONSIGNEE_ID, O.C_COMPANY , O.C_ADDRESS ,O.C_CITY, O.C_PROV , O.C_ZIP, O.C_COUNTRY_CODE , O.C_COUNTRY, O.C_VAT , O.C_CONTACT1 ,O.C_CONTACT2 , O.C_EMAIL1 , O.C_EMAIL2 , O.C_PHONE1 , O.C_PHONE2, O.C_FAX1 , O.C_FAX2 , O.BILL_TO_ID , O.B_COMPANY , O.B_ADDRESS , O.B_CITY , O.B_STATE , O.B_ZIP, O.B_COUNTRY_CODE, O.B_COUNTRY , O.B_CONTACT1 , O.B_CONTACT2, O.B_EMAIL1 , O.B_EMAIL2, O.B_PHONE1 , O.B_PHONE2 ,O.B_FAX1 , O.B_FAX2 , O.B_VAT , O.CARRIER_ID , O.CARRIER_NAME, O.CARRIER_ADDRESS, O.CARRIER_CITY , O.CARRIER_PROV , O.CARRIER_ZIP , O.CARRIER_COUNTRY_CODE , O.CARRIER_COUNTRY , O.CARRIER_PHONE , O.DEPARTURE_DATE ,O.TRAILER_NUMBER , O.TRAILER_OWNER , O.DRIVER_NAME, O.PRO_NUMBER, O.DOOR, O.ROUTE , O.STOP , O.STAGE ,O.TRANSPORTATION_MODE, O.TRANSPORTATION_SERVICE , O.INCO_TERMS , O.PAYMENT_TERM , O.CONTAINER_TYPE , O.CONTAINER_QTY ,O.LOAD_ID , O.EXTERNAL_LOAD_ID , O.COUNTRY_OF_ORIGIN , O.COUNTRY_DESTINATION , O.DISCHARGE_PLACE , O.DELIVERY_PLACE ,O.HANDLING_TYPE , O.BATCH_FLAG, O.SHIP_TOGETHER , O.SORTATION_LOCATION , O.ORDER_BREAK , O.ENABLE_PACKING ,O.PACKING_LOCATION , O.UDF1 , O.UDF2 , O.UDF3, O.UDF4 , O.UDF5, O.UDF6 , O.UDF7, O.UDF8 ,O.UDF9 , O.UDF10 , O.BUYER_PO , O.ORDER_VALUE , O.INTRANSIT_ID , O.UPDATE_SOURCE , O.SPLIT_ORDERS , O.APPT_STATUS, O.REMARK, O.REMARK2 , O.IS_ACTIVE , O.TS, O.CREATED_BY, O.CREATED_DATE,O.UPDATED_BY , O.UPDATED_DATE,I.DEST_WH_DB_ID,WD.WAVE_ID,O.CORE_RELEASE_ID,O.IS_DECLARATION,,O.C_COUNTIES,O.SHIP_NAME,O.VOYAGE,O.DESTINATION_PORT,O.DEPARTURE_PORT,O.B_COUNTIES,O.C_STREET,O.B_STREET,O.IS_COD,O.IS_INVOICE_PRINT ,O.OPERATOR,O.OPERATING_DATE,O.ABNORMAL,O.HANDLING,O.DECLARATIONS_TYPE,O.WORK_ORDER,O.TRANSSHIPMENT_PORT,O.IS_PALLET_AUDIT ";
            List<OrdersInfo> ordersInfos = new List<OrdersInfo>();
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CORE_RELEASE_ID", CRID), new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            OrdersInfo ordersInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                ordersInfo = new OrdersInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["DELIVERY_DATE2"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE2"].ToString()) : (CS00002 = new DateTime()), reader["PRIORITY"].ToString(), reader["TYPE"].ToString(), reader["ORDER_GROUP"].ToString(), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERNAL_ORDER_ID2"].ToString(), reader["REFERENCE_NUM"].ToString(), reader["TRADING_PARTNER"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CONSIGNEE_ID"].ToString(), reader["C_COMPANY"].ToString(), reader["C_ADDRESS"].ToString(), reader["C_CITY"].ToString(), reader["C_PROV"].ToString(), reader["C_ZIP"].ToString(), reader["C_COUNTRY_CODE"].ToString(), reader["C_COUNTRY"].ToString(), reader["C_VAT"].ToString(), reader["C_CONTACT1"].ToString(), reader["C_CONTACT2"].ToString(), reader["C_EMAIL1"].ToString(), reader["C_EMAIL2"].ToString(), reader["C_PHONE1"].ToString(), reader["C_PHONE2"].ToString(), reader["C_FAX1"].ToString(), reader["C_FAX2"].ToString(), reader["BILL_TO_ID"].ToString(), reader["B_COMPANY"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_VAT"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY_CODE"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_PHONE"].ToString(), (reader["DEPARTURE_DATE"].ToString() != "") ? DateTime.Parse(reader["DEPARTURE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["DRIVER_NAME"].ToString(), reader["PRO_NUMBER"].ToString(), reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["STAGE"].ToString(), reader["TRANSPORTATION_MODE"].ToString(), reader["TRANSPORTATION_SERVICE"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["LOAD_ID"].ToString(), reader["EXTERNAL_LOAD_ID"].ToString(), reader["COUNTRY_OF_ORIGIN"].ToString(), reader["COUNTRY_DESTINATION"].ToString(), reader["DISCHARGE_PLACE"].ToString(), reader["DELIVERY_PLACE"].ToString(), reader["HANDLING_TYPE"].ToString(), reader["BATCH_FLAG"].ToString(), reader["SHIP_TOGETHER"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["ORDER_BREAK"].ToString(), reader["ENABLE_PACKING"].ToString(), reader["PACKING_LOCATION"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["BUYER_PO"].ToString(), (reader["ORDER_VALUE"].ToString() != "") ? decimal.Parse(reader["ORDER_VALUE"].ToString()) : 0M, reader["INTRANSIT_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["SPLIT_ORDERS"].ToString(), reader["APPT_STATUS"].ToString(), reader["REMARK"].ToString(), reader["REMARK2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["DEST_WH_DB_ID"].ToString(), reader["STATUS_D"].ToString(), reader["TYPE_D"].ToString(), reader["CORE_RELEASE_ID"].ToString(), reader["IS_DECLARATION"].ToString(), reader["C_COUNTIES"].ToString(), reader["SHIP_NAME"].ToString(), reader["VOYAGE"].ToString(), reader["DESTINATION_PORT"].ToString(), reader["DEPARTURE_PORT"].ToString(), reader["TRANSSHIPMENT_PORT"].ToString(), reader["B_COUNTIES"].ToString(), reader["B_STREET"].ToString(), reader["C_STREET"].ToString(), reader["IS_COD"].ToString(), reader["IS_INVOICE_PRINT"].ToString(), reader["OPERATOR"].ToString(), (reader["OPERATING_DATE"].ToString() != "") ? DateTime.Parse(reader["OPERATING_DATE"].ToString()) : (CS00002 = new DateTime()), reader["ABNORMAL"].ToString(), reader["HANDLING"].ToString(), reader["DECLARATIONS_TYPE"].ToString(), reader["WORK_ORDER"].ToString(), reader["IS_PALLET_AUDIT"].ToString());
                ordersInfos.Add(ordersInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return ordersInfos;
        }

        public OrdersInfo GetOrdersByExternalID(string externalID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDERS_GROUP_BY.Replace("@_@", whLoginID) + " WHERE O.EXTERN_ORDER_ID = @EXTERN_ORDER_ID " + " GROUP BY  O.ROWID , O.STATUS  ,O.WH_ID , O.ORDER_ID , O.OWNER_ID, O.ORDER_DATE , O.REQUESTED_SHIP_DATE,O.ACTUAL_SHIP_DATE , O.DELIVERY_DATE , O.DELIVERY_DATE2 , O.PRIORITY , T2.[TEXT] , O.TYPE ,O.ORDER_GROUP, O.OPEN_QTY , T.[TEXT] , O.STATUS , O.EXTERN_ORDER_ID , O.EXTERNAL_ORDER_ID2 ,O.ORDER_GROUP, O.OPEN_QTY , T.[TEXT] , O.STATUS , O.EXTERN_ORDER_ID , O.EXTERNAL_ORDER_ID2 ,O.REFERENCE_NUM , O.TRADING_PARTNER, O.RECEIPT_ID , O.CONSIGNEE_ID, O.C_COMPANY , O.C_ADDRESS ,O.C_CITY, O.C_PROV , O.C_ZIP, O.C_COUNTRY_CODE , O.C_COUNTRY, O.C_VAT , O.C_CONTACT1 ,O.C_CONTACT2 , O.C_EMAIL1 , O.C_EMAIL2 , O.C_PHONE1 , O.C_PHONE2, O.C_FAX1 , O.C_FAX2 , O.BILL_TO_ID , O.B_COMPANY , O.B_ADDRESS , O.B_CITY , O.B_STATE , O.B_ZIP, O.B_COUNTRY_CODE, O.B_COUNTRY , O.B_CONTACT1 , O.B_CONTACT2, O.B_EMAIL1 , O.B_EMAIL2, O.B_PHONE1 , O.B_PHONE2 ,O.B_FAX1 , O.B_FAX2 , O.B_VAT , O.CARRIER_ID , O.CARRIER_NAME, O.CARRIER_ADDRESS, O.CARRIER_CITY , O.CARRIER_PROV , O.CARRIER_ZIP , O.CARRIER_COUNTRY_CODE , O.CARRIER_COUNTRY , O.CARRIER_PHONE , O.DEPARTURE_DATE ,O.TRAILER_NUMBER , O.TRAILER_OWNER , O.DRIVER_NAME, O.PRO_NUMBER, O.DOOR, O.ROUTE , O.STOP , O.STAGE ,O.TRANSPORTATION_MODE, O.TRANSPORTATION_SERVICE , O.INCO_TERMS , O.PAYMENT_TERM , O.CONTAINER_TYPE , O.CONTAINER_QTY ,O.LOAD_ID , O.EXTERNAL_LOAD_ID , O.COUNTRY_OF_ORIGIN , O.COUNTRY_DESTINATION , O.DISCHARGE_PLACE , O.DELIVERY_PLACE ,O.HANDLING_TYPE , O.BATCH_FLAG, O.SHIP_TOGETHER , O.SORTATION_LOCATION , O.ORDER_BREAK , O.ENABLE_PACKING ,O.PACKING_LOCATION , O.UDF1 , O.UDF2 , O.UDF3, O.UDF4 , O.UDF5, O.UDF6 , O.UDF7, O.UDF8 ,O.UDF9 , O.UDF10 , O.BUYER_PO , O.ORDER_VALUE , O.INTRANSIT_ID , O.UPDATE_SOURCE , O.SPLIT_ORDERS , O.APPT_STATUS, O.REMARK, O.REMARK2 , O.IS_ACTIVE , O.TS, O.CREATED_BY, O.CREATED_DATE,O.UPDATED_BY , O.UPDATED_DATE,I.DEST_WH_DB_ID,WD.WAVE_ID,O.CORE_RELEASE_ID,O.IS_DECLARATION,O.C_COUNTIES,O.SHIP_NAME,O.VOYAGE,O.DESTINATION_PORT,O.DEPARTURE_PORT,O.B_COUNTIES,O.C_STREET,O.B_STREET,O.IS_COD,O.IS_INVOICE_PRINT,O.OPERATOR,O.OPERATING_DATE,O.ABNORMAL,O.HANDLING,O.DECLARATIONS_TYPE,O.WORK_ORDER,O.TRANSSHIPMENT_PORT,O.IS_PALLET_AUDIT  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@EXTERN_ORDER_ID", externalID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            OrdersInfo ordersInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    ordersInfo = new OrdersInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE2"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE2"].ToString()) : (CS00003 = new DateTime()), reader["PRIORITY"].ToString(), reader["TYPE"].ToString(), reader["ORDER_GROUP"].ToString(), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERNAL_ORDER_ID2"].ToString(), reader["REFERENCE_NUM"].ToString(), reader["TRADING_PARTNER"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CONSIGNEE_ID"].ToString(), reader["C_COMPANY"].ToString(), reader["C_ADDRESS"].ToString(), reader["C_CITY"].ToString(), reader["C_PROV"].ToString(), reader["C_ZIP"].ToString(), reader["C_COUNTRY_CODE"].ToString(), reader["C_COUNTRY"].ToString(), reader["C_VAT"].ToString(), reader["C_CONTACT1"].ToString(), reader["C_CONTACT2"].ToString(), reader["C_EMAIL1"].ToString(), reader["C_EMAIL2"].ToString(), reader["C_PHONE1"].ToString(), reader["C_PHONE2"].ToString(), reader["C_FAX1"].ToString(), reader["C_FAX2"].ToString(), reader["BILL_TO_ID"].ToString(), reader["B_COMPANY"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_VAT"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY_CODE"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_PHONE"].ToString(), (reader["DEPARTURE_DATE"].ToString() != "") ? DateTime.Parse(reader["DEPARTURE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["DRIVER_NAME"].ToString(), reader["PRO_NUMBER"].ToString(), reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["STAGE"].ToString(), reader["TRANSPORTATION_MODE"].ToString(), reader["TRANSPORTATION_SERVICE"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["LOAD_ID"].ToString(), reader["EXTERNAL_LOAD_ID"].ToString(), reader["COUNTRY_OF_ORIGIN"].ToString(), reader["COUNTRY_DESTINATION"].ToString(), reader["DISCHARGE_PLACE"].ToString(), reader["DELIVERY_PLACE"].ToString(), reader["HANDLING_TYPE"].ToString(), reader["BATCH_FLAG"].ToString(), reader["SHIP_TOGETHER"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["ORDER_BREAK"].ToString(), reader["ENABLE_PACKING"].ToString(), reader["PACKING_LOCATION"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["BUYER_PO"].ToString(), (reader["ORDER_VALUE"].ToString() != "") ? decimal.Parse(reader["ORDER_VALUE"].ToString()) : 0M, reader["INTRANSIT_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["SPLIT_ORDERS"].ToString(), reader["APPT_STATUS"].ToString(), reader["REMARK"].ToString(), reader["REMARK2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["DEST_WH_DB_ID"].ToString(), reader["STATUS_D"].ToString(), reader["TYPE_D"].ToString(), reader["CORE_RELEASE_ID"].ToString(), reader["IS_DECLARATION"].ToString(), reader["C_COUNTIES"].ToString(), reader["SHIP_NAME"].ToString(), reader["VOYAGE"].ToString(), reader["DESTINATION_PORT"].ToString(), reader["DEPARTURE_PORT"].ToString(), reader["TRANSSHIPMENT_PORT"].ToString(), reader["B_COUNTIES"].ToString(), reader["B_STREET"].ToString(), reader["C_STREET"].ToString(), reader["IS_COD"].ToString(), reader["IS_INVOICE_PRINT"].ToString(), reader["OPERATOR"].ToString(), (reader["OPERATING_DATE"].ToString() != "") ? DateTime.Parse(reader["OPERATING_DATE"].ToString()) : new DateTime(), reader["ABNORMAL"].ToString(), reader["HANDLING"].ToString(), reader["DECLARATIONS_TYPE"].ToString(), reader["WORK_ORDER"].ToString(), reader["IS_PALLET_AUDIT"].ToString());
                }
            }
            return ordersInfo;
        }

        public OrdersInfo GetOrdersByExternalID(DataBase dataBase, DbTransaction tran, string externalID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDERS_GROUP_BY.Replace("@_@", whLoginID) + " WHERE O.EXTERN_ORDER_ID = @EXTERN_ORDER_ID " + " GROUP BY  O.ROWID , O.STATUS  ,O.WH_ID , O.ORDER_ID , O.OWNER_ID, O.ORDER_DATE , O.REQUESTED_SHIP_DATE,O.ACTUAL_SHIP_DATE , O.DELIVERY_DATE , O.DELIVERY_DATE2 , O.PRIORITY , T2.[TEXT] , O.TYPE ,O.ORDER_GROUP, O.OPEN_QTY , T.[TEXT] , O.STATUS , O.EXTERN_ORDER_ID , O.EXTERNAL_ORDER_ID2 ,O.ORDER_GROUP, O.OPEN_QTY , T.[TEXT] , O.STATUS , O.EXTERN_ORDER_ID , O.EXTERNAL_ORDER_ID2 ,O.REFERENCE_NUM , O.TRADING_PARTNER, O.RECEIPT_ID , O.CONSIGNEE_ID, O.C_COMPANY , O.C_ADDRESS ,O.C_CITY, O.C_PROV , O.C_ZIP, O.C_COUNTRY_CODE , O.C_COUNTRY, O.C_VAT , O.C_CONTACT1 ,O.C_CONTACT2 , O.C_EMAIL1 , O.C_EMAIL2 , O.C_PHONE1 , O.C_PHONE2, O.C_FAX1 , O.C_FAX2 , O.BILL_TO_ID , O.B_COMPANY , O.B_ADDRESS , O.B_CITY , O.B_STATE , O.B_ZIP, O.B_COUNTRY_CODE, O.B_COUNTRY , O.B_CONTACT1 , O.B_CONTACT2, O.B_EMAIL1 , O.B_EMAIL2, O.B_PHONE1 , O.B_PHONE2 ,O.B_FAX1 , O.B_FAX2 , O.B_VAT , O.CARRIER_ID , O.CARRIER_NAME, O.CARRIER_ADDRESS, O.CARRIER_CITY , O.CARRIER_PROV , O.CARRIER_ZIP , O.CARRIER_COUNTRY_CODE , O.CARRIER_COUNTRY , O.CARRIER_PHONE , O.DEPARTURE_DATE ,O.TRAILER_NUMBER , O.TRAILER_OWNER , O.DRIVER_NAME, O.PRO_NUMBER, O.DOOR, O.ROUTE , O.STOP , O.STAGE ,O.TRANSPORTATION_MODE, O.TRANSPORTATION_SERVICE , O.INCO_TERMS , O.PAYMENT_TERM , O.CONTAINER_TYPE , O.CONTAINER_QTY ,O.LOAD_ID , O.EXTERNAL_LOAD_ID , O.COUNTRY_OF_ORIGIN , O.COUNTRY_DESTINATION , O.DISCHARGE_PLACE , O.DELIVERY_PLACE ,O.HANDLING_TYPE , O.BATCH_FLAG, O.SHIP_TOGETHER , O.SORTATION_LOCATION , O.ORDER_BREAK , O.ENABLE_PACKING ,O.PACKING_LOCATION , O.UDF1 , O.UDF2 , O.UDF3, O.UDF4 , O.UDF5, O.UDF6 , O.UDF7, O.UDF8 ,O.UDF9 , O.UDF10 , O.BUYER_PO , O.ORDER_VALUE , O.INTRANSIT_ID , O.UPDATE_SOURCE , O.SPLIT_ORDERS , O.APPT_STATUS, O.REMARK, O.REMARK2 , O.IS_ACTIVE , O.TS, O.CREATED_BY, O.CREATED_DATE,O.UPDATED_BY , O.UPDATED_DATE,I.DEST_WH_DB_ID,WD.WAVE_ID,O.CORE_RELEASE_ID,O.IS_DECLARATION,O.C_COUNTIES,O.SHIP_NAME,O.VOYAGE,O.DESTINATION_PORT,O.DEPARTURE_PORT,O.B_COUNTIES,O.C_STREET,O.B_STREET,O.IS_COD,O.IS_INVOICE_PRINT,O.OPERATOR,O.OPERATING_DATE,O.ABNORMAL,O.HANDLING,O.DECLARATIONS_TYPE,O.WORK_ORDER,O.TRANSSHIPMENT_PORT,O.IS_PALLET_AUDIT ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@EXTERN_ORDER_ID", externalID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            OrdersInfo ordersInfo = null;
            using (IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    ordersInfo = new OrdersInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE2"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE2"].ToString()) : (CS00003 = new DateTime()), reader["PRIORITY"].ToString(), reader["TYPE"].ToString(), reader["ORDER_GROUP"].ToString(), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERNAL_ORDER_ID2"].ToString(), reader["REFERENCE_NUM"].ToString(), reader["TRADING_PARTNER"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CONSIGNEE_ID"].ToString(), reader["C_COMPANY"].ToString(), reader["C_ADDRESS"].ToString(), reader["C_CITY"].ToString(), reader["C_PROV"].ToString(), reader["C_ZIP"].ToString(), reader["C_COUNTRY_CODE"].ToString(), reader["C_COUNTRY"].ToString(), reader["C_VAT"].ToString(), reader["C_CONTACT1"].ToString(), reader["C_CONTACT2"].ToString(), reader["C_EMAIL1"].ToString(), reader["C_EMAIL2"].ToString(), reader["C_PHONE1"].ToString(), reader["C_PHONE2"].ToString(), reader["C_FAX1"].ToString(), reader["C_FAX2"].ToString(), reader["BILL_TO_ID"].ToString(), reader["B_COMPANY"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_VAT"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY_CODE"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_PHONE"].ToString(), (reader["DEPARTURE_DATE"].ToString() != "") ? DateTime.Parse(reader["DEPARTURE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["DRIVER_NAME"].ToString(), reader["PRO_NUMBER"].ToString(), reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["STAGE"].ToString(), reader["TRANSPORTATION_MODE"].ToString(), reader["TRANSPORTATION_SERVICE"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["LOAD_ID"].ToString(), reader["EXTERNAL_LOAD_ID"].ToString(), reader["COUNTRY_OF_ORIGIN"].ToString(), reader["COUNTRY_DESTINATION"].ToString(), reader["DISCHARGE_PLACE"].ToString(), reader["DELIVERY_PLACE"].ToString(), reader["HANDLING_TYPE"].ToString(), reader["BATCH_FLAG"].ToString(), reader["SHIP_TOGETHER"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["ORDER_BREAK"].ToString(), reader["ENABLE_PACKING"].ToString(), reader["PACKING_LOCATION"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["BUYER_PO"].ToString(), (reader["ORDER_VALUE"].ToString() != "") ? decimal.Parse(reader["ORDER_VALUE"].ToString()) : 0M, reader["INTRANSIT_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["SPLIT_ORDERS"].ToString(), reader["APPT_STATUS"].ToString(), reader["REMARK"].ToString(), reader["REMARK2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["DEST_WH_DB_ID"].ToString(), reader["STATUS_D"].ToString(), reader["TYPE_D"].ToString(), reader["CORE_RELEASE_ID"].ToString(), reader["IS_DECLARATION"].ToString(), reader["C_COUNTIES"].ToString(), reader["SHIP_NAME"].ToString(), reader["VOYAGE"].ToString(), reader["DESTINATION_PORT"].ToString(), reader["DEPARTURE_PORT"].ToString(), reader["TRANSSHIPMENT_PORT"].ToString(), reader["B_COUNTIES"].ToString(), reader["B_STREET"].ToString(), reader["C_STREET"].ToString(), reader["IS_COD"].ToString(), reader["IS_INVOICE_PRINT"].ToString(), reader["OPERATOR"].ToString(), (reader["OPERATING_DATE"].ToString() != "") ? DateTime.Parse(reader["OPERATING_DATE"].ToString()) : new DateTime(), reader["ABNORMAL"].ToString(), reader["HANDLING"].ToString(), reader["DECLARATIONS_TYPE"].ToString(), reader["WORK_ORDER"].ToString(), reader["IS_PALLET_AUDIT"].ToString());
                }
            }
            return ordersInfo;
        }

        public OrdersInfo GetOrdersByID(string ordersID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDERS_GROUP_BY.Replace("@_@", whLoginID) + " WHERE O.ORDER_ID = @ORDER_ID " + " GROUP BY  O.ROWID , O.STATUS  ,O.WH_ID , O.ORDER_ID , O.OWNER_ID, O.ORDER_DATE , O.REQUESTED_SHIP_DATE,O.ACTUAL_SHIP_DATE , O.DELIVERY_DATE , O.DELIVERY_DATE2 , O.PRIORITY , T2.[TEXT] , O.TYPE ,O.ORDER_GROUP, O.OPEN_QTY , T.[TEXT] , O.STATUS , O.EXTERN_ORDER_ID , O.EXTERNAL_ORDER_ID2 ,O.ORDER_GROUP, O.OPEN_QTY , T.[TEXT] , O.STATUS , O.EXTERN_ORDER_ID , O.EXTERNAL_ORDER_ID2 ,O.REFERENCE_NUM , O.TRADING_PARTNER, O.RECEIPT_ID , O.CONSIGNEE_ID, O.C_COMPANY , O.C_ADDRESS ,O.C_CITY, O.C_PROV , O.C_ZIP, O.C_COUNTRY_CODE , O.C_COUNTRY, O.C_VAT , O.C_CONTACT1 ,O.C_CONTACT2 , O.C_EMAIL1 , O.C_EMAIL2 , O.C_PHONE1 , O.C_PHONE2, O.C_FAX1 , O.C_FAX2 , O.BILL_TO_ID , O.B_COMPANY , O.B_ADDRESS , O.B_CITY , O.B_STATE , O.B_ZIP, O.B_COUNTRY_CODE, O.B_COUNTRY , O.B_CONTACT1 , O.B_CONTACT2, O.B_EMAIL1 , O.B_EMAIL2, O.B_PHONE1 , O.B_PHONE2 ,O.B_FAX1 , O.B_FAX2 , O.B_VAT , O.CARRIER_ID , O.CARRIER_NAME, O.CARRIER_ADDRESS, O.CARRIER_CITY , O.CARRIER_PROV , O.CARRIER_ZIP , O.CARRIER_COUNTRY_CODE , O.CARRIER_COUNTRY , O.CARRIER_PHONE , O.DEPARTURE_DATE ,O.TRAILER_NUMBER , O.TRAILER_OWNER , O.DRIVER_NAME, O.PRO_NUMBER, O.DOOR, O.ROUTE , O.STOP , O.STAGE ,O.TRANSPORTATION_MODE, O.TRANSPORTATION_SERVICE , O.INCO_TERMS , O.PAYMENT_TERM , O.CONTAINER_TYPE , O.CONTAINER_QTY ,O.LOAD_ID , O.EXTERNAL_LOAD_ID , O.COUNTRY_OF_ORIGIN , O.COUNTRY_DESTINATION , O.DISCHARGE_PLACE , O.DELIVERY_PLACE ,O.HANDLING_TYPE , O.BATCH_FLAG, O.SHIP_TOGETHER , O.SORTATION_LOCATION , O.ORDER_BREAK , O.ENABLE_PACKING ,O.PACKING_LOCATION , O.UDF1 , O.UDF2 , O.UDF3, O.UDF4 , O.UDF5, O.UDF6 , O.UDF7, O.UDF8 ,O.UDF9 , O.UDF10 , O.BUYER_PO , O.ORDER_VALUE , O.INTRANSIT_ID , O.UPDATE_SOURCE , O.SPLIT_ORDERS , O.APPT_STATUS, O.REMARK, O.REMARK2 , O.IS_ACTIVE , O.TS, O.CREATED_BY, O.CREATED_DATE,O.UPDATED_BY , O.UPDATED_DATE,I.DEST_WH_DB_ID,WD.WAVE_ID,O.CORE_RELEASE_ID,O.IS_DECLARATION ,O.C_COUNTIES,O.SHIP_NAME,O.VOYAGE,O.DESTINATION_PORT,O.DEPARTURE_PORT,O.B_COUNTIES,O.C_STREET,O.B_STREET,O.IS_COD,O.IS_INVOICE_PRINT,O.OPERATOR,O.OPERATING_DATE,O.ABNORMAL,O.HANDLING,O.DECLARATIONS_TYPE,O.WORK_ORDER,O.TRANSSHIPMENT_PORT,O.IS_PALLET_AUDIT ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", ordersID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            OrdersInfo ordersInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    ordersInfo = new OrdersInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE2"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE2"].ToString()) : (CS00003 = new DateTime()), reader["PRIORITY"].ToString(), reader["TYPE"].ToString(), reader["ORDER_GROUP"].ToString(), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERNAL_ORDER_ID2"].ToString(), reader["REFERENCE_NUM"].ToString(), reader["TRADING_PARTNER"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CONSIGNEE_ID"].ToString(), reader["C_COMPANY"].ToString(), reader["C_ADDRESS"].ToString(), reader["C_CITY"].ToString(), reader["C_PROV"].ToString(), reader["C_ZIP"].ToString(), reader["C_COUNTRY_CODE"].ToString(), reader["C_COUNTRY"].ToString(), reader["C_VAT"].ToString(), reader["C_CONTACT1"].ToString(), reader["C_CONTACT2"].ToString(), reader["C_EMAIL1"].ToString(), reader["C_EMAIL2"].ToString(), reader["C_PHONE1"].ToString(), reader["C_PHONE2"].ToString(), reader["C_FAX1"].ToString(), reader["C_FAX2"].ToString(), reader["BILL_TO_ID"].ToString(), reader["B_COMPANY"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_VAT"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY_CODE"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_PHONE"].ToString(), (reader["DEPARTURE_DATE"].ToString() != "") ? DateTime.Parse(reader["DEPARTURE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["DRIVER_NAME"].ToString(), reader["PRO_NUMBER"].ToString(), reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["STAGE"].ToString(), reader["TRANSPORTATION_MODE"].ToString(), reader["TRANSPORTATION_SERVICE"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["LOAD_ID"].ToString(), reader["EXTERNAL_LOAD_ID"].ToString(), reader["COUNTRY_OF_ORIGIN"].ToString(), reader["COUNTRY_DESTINATION"].ToString(), reader["DISCHARGE_PLACE"].ToString(), reader["DELIVERY_PLACE"].ToString(), reader["HANDLING_TYPE"].ToString(), reader["BATCH_FLAG"].ToString(), reader["SHIP_TOGETHER"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["ORDER_BREAK"].ToString(), reader["ENABLE_PACKING"].ToString(), reader["PACKING_LOCATION"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["BUYER_PO"].ToString(), (reader["ORDER_VALUE"].ToString() != "") ? decimal.Parse(reader["ORDER_VALUE"].ToString()) : 0M, reader["INTRANSIT_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["SPLIT_ORDERS"].ToString(), reader["APPT_STATUS"].ToString(), reader["REMARK"].ToString(), reader["REMARK2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["DEST_WH_DB_ID"].ToString(), reader["STATUS_D"].ToString(), reader["TYPE_D"].ToString(), reader["CORE_RELEASE_ID"].ToString(), reader["IS_DECLARATION"].ToString(), reader["C_COUNTIES"].ToString(), reader["SHIP_NAME"].ToString(), reader["VOYAGE"].ToString(), reader["DESTINATION_PORT"].ToString(), reader["DEPARTURE_PORT"].ToString(), reader["TRANSSHIPMENT_PORT"].ToString(), reader["B_COUNTIES"].ToString(), reader["B_STREET"].ToString(), reader["C_STREET"].ToString(), reader["IS_COD"].ToString(), reader["IS_INVOICE_PRINT"].ToString(), reader["OPERATOR"].ToString(), (reader["OPERATING_DATE"].ToString() != "") ? DateTime.Parse(reader["OPERATING_DATE"].ToString()) : new DateTime(), reader["ABNORMAL"].ToString(), reader["HANDLING"].ToString(), reader["DECLARATIONS_TYPE"].ToString(), reader["WORK_ORDER"].ToString(), reader["IS_PALLET_AUDIT"].ToString());
                }
            }
            return ordersInfo;
        }

        public OrdersInfo GetOrdersByID(DataBase dataBase, DbTransaction tran, string ordersID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDERS.Replace("@_@", whLoginID) + " WHERE O.ORDER_ID = @ORDER_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", ordersID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            OrdersInfo ordersInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                ordersInfo = new OrdersInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE2"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE2"].ToString()) : (CS00003 = new DateTime()), reader["PRIORITY"].ToString(), reader["TYPE"].ToString(), reader["ORDER_GROUP"].ToString(), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERNAL_ORDER_ID2"].ToString(), reader["REFERENCE_NUM"].ToString(), reader["TRADING_PARTNER"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CONSIGNEE_ID"].ToString(), reader["C_COMPANY"].ToString(), reader["C_ADDRESS"].ToString(), reader["C_CITY"].ToString(), reader["C_PROV"].ToString(), reader["C_ZIP"].ToString(), reader["C_COUNTRY_CODE"].ToString(), reader["C_COUNTRY"].ToString(), reader["C_VAT"].ToString(), reader["C_CONTACT1"].ToString(), reader["C_CONTACT2"].ToString(), reader["C_EMAIL1"].ToString(), reader["C_EMAIL2"].ToString(), reader["C_PHONE1"].ToString(), reader["C_PHONE2"].ToString(), reader["C_FAX1"].ToString(), reader["C_FAX2"].ToString(), reader["BILL_TO_ID"].ToString(), reader["B_COMPANY"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_VAT"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY_CODE"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_PHONE"].ToString(), (reader["DEPARTURE_DATE"].ToString() != "") ? DateTime.Parse(reader["DEPARTURE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["DRIVER_NAME"].ToString(), reader["PRO_NUMBER"].ToString(), reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["STAGE"].ToString(), reader["TRANSPORTATION_MODE"].ToString(), reader["TRANSPORTATION_SERVICE"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["LOAD_ID"].ToString(), reader["EXTERNAL_LOAD_ID"].ToString(), reader["COUNTRY_OF_ORIGIN"].ToString(), reader["COUNTRY_DESTINATION"].ToString(), reader["DISCHARGE_PLACE"].ToString(), reader["DELIVERY_PLACE"].ToString(), reader["HANDLING_TYPE"].ToString(), reader["BATCH_FLAG"].ToString(), reader["SHIP_TOGETHER"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["ORDER_BREAK"].ToString(), reader["ENABLE_PACKING"].ToString(), reader["PACKING_LOCATION"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["BUYER_PO"].ToString(), (reader["ORDER_VALUE"].ToString() != "") ? decimal.Parse(reader["ORDER_VALUE"].ToString()) : 0M, reader["INTRANSIT_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["SPLIT_ORDERS"].ToString(), reader["APPT_STATUS"].ToString(), reader["REMARK"].ToString(), reader["REMARK2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["DEST_WH_DB_ID"].ToString(), reader["STATUS_D"].ToString(), reader["TYPE_D"].ToString(), reader["CORE_RELEASE_ID"].ToString(), reader["IS_DECLARATION"].ToString(), reader["C_COUNTIES"].ToString(), reader["SHIP_NAME"].ToString(), reader["VOYAGE"].ToString(), reader["DESTINATION_PORT"].ToString(), reader["DEPARTURE_PORT"].ToString(), reader["TRANSSHIPMENT_PORT"].ToString(), reader["B_COUNTIES"].ToString(), reader["B_STREET"].ToString(), reader["C_STREET"].ToString(), reader["IS_COD"].ToString(), reader["IS_INVOICE_PRINT"].ToString(), reader["OPERATOR"].ToString(), (reader["OPERATING_DATE"].ToString() != "") ? DateTime.Parse(reader["OPERATING_DATE"].ToString()) : new DateTime(), reader["ABNORMAL"].ToString(), reader["HANDLING"].ToString(), reader["DECLARATIONS_TYPE"].ToString(), reader["WORK_ORDER"].ToString(), reader["IS_PALLET_AUDIT"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return ordersInfo;
        }

        public DataSet GetOrdersByQueryList(List<string> sqlWhere, OrdersQueryEntity ordersQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_ORDERS.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!ordersQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, ordersQuery.CurrentPage, ordersQuery.PageSize, ordersQuery.SortField, ordersQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetOrdersByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, OrdersQueryEntity ordersQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_ORDERS.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!ordersQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, ordersQuery.CurrentPage, ordersQuery.PageSize, ordersQuery.SortField, ordersQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<OrdersInfo> GetOrdersByReferenceNumAndType(DataBase dataBase, DbTransaction tran, string ReferenceNum, string Type, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_ORDERS.Replace("@_@", whLoginID) + " WHERE O.REFERENCE_NUM = @REFERENCE_NUM AND TYPE = @TYPE ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@REFERENCE_NUM", ReferenceNum), new SqlParameter("@TYPE", Type), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrdersInfo> ordersList = new List<OrdersInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                OrdersInfo ordersInfo = new OrdersInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["DELIVERY_DATE2"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE2"].ToString()) : (CS00002 = new DateTime()), reader["PRIORITY"].ToString(), reader["TYPE"].ToString(), reader["ORDER_GROUP"].ToString(), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERNAL_ORDER_ID2"].ToString(), reader["REFERENCE_NUM"].ToString(), reader["TRADING_PARTNER"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CONSIGNEE_ID"].ToString(), reader["C_COMPANY"].ToString(), reader["C_ADDRESS"].ToString(), reader["C_CITY"].ToString(), reader["C_PROV"].ToString(), reader["C_ZIP"].ToString(), reader["C_COUNTRY_CODE"].ToString(), reader["C_COUNTRY"].ToString(), reader["C_VAT"].ToString(), reader["C_CONTACT1"].ToString(), reader["C_CONTACT2"].ToString(), reader["C_EMAIL1"].ToString(), reader["C_EMAIL2"].ToString(), reader["C_PHONE1"].ToString(), reader["C_PHONE2"].ToString(), reader["C_FAX1"].ToString(), reader["C_FAX2"].ToString(), reader["BILL_TO_ID"].ToString(), reader["B_COMPANY"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_VAT"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY_CODE"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_PHONE"].ToString(), (reader["DEPARTURE_DATE"].ToString() != "") ? DateTime.Parse(reader["DEPARTURE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["DRIVER_NAME"].ToString(), reader["PRO_NUMBER"].ToString(), reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["STAGE"].ToString(), reader["TRANSPORTATION_MODE"].ToString(), reader["TRANSPORTATION_SERVICE"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["LOAD_ID"].ToString(), reader["EXTERNAL_LOAD_ID"].ToString(), reader["COUNTRY_OF_ORIGIN"].ToString(), reader["COUNTRY_DESTINATION"].ToString(), reader["DISCHARGE_PLACE"].ToString(), reader["DELIVERY_PLACE"].ToString(), reader["HANDLING_TYPE"].ToString(), reader["BATCH_FLAG"].ToString(), reader["SHIP_TOGETHER"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["ORDER_BREAK"].ToString(), reader["ENABLE_PACKING"].ToString(), reader["PACKING_LOCATION"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["BUYER_PO"].ToString(), (reader["ORDER_VALUE"].ToString() != "") ? decimal.Parse(reader["ORDER_VALUE"].ToString()) : 0M, reader["INTRANSIT_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["SPLIT_ORDERS"].ToString(), reader["APPT_STATUS"].ToString(), reader["REMARK"].ToString(), reader["REMARK2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["DEST_WH_DB_ID"].ToString(), reader["STATUS_D"].ToString(), reader["TYPE_D"].ToString(), reader["CORE_RELEASE_ID"].ToString(), reader["IS_DECLARATION"].ToString(), reader["C_COUNTIES"].ToString(), reader["SHIP_NAME"].ToString(), reader["VOYAGE"].ToString(), reader["DESTINATION_PORT"].ToString(), reader["DEPARTURE_PORT"].ToString(), reader["TRANSSHIPMENT_PORT"].ToString(), reader["B_COUNTIES"].ToString(), reader["B_STREET"].ToString(), reader["C_STREET"].ToString(), reader["IS_COD"].ToString(), reader["IS_INVOICE_PRINT"].ToString(), reader["OPERATOR"].ToString(), (reader["OPERATING_DATE"].ToString() != "") ? DateTime.Parse(reader["OPERATING_DATE"].ToString()) : (CS00002 = new DateTime()), reader["ABNORMAL"].ToString(), reader["HANDLING"].ToString(), reader["DECLARATIONS_TYPE"].ToString(), reader["WORK_ORDER"].ToString(), reader["IS_PALLET_AUDIT"].ToString());
                ordersList.Add(ordersInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return ordersList;
        }

        public List<OrdersInfo> GetOrdersInfoByWaveTemplate(List<string> sqlWhere, string MaxOrders, string whLoginID, PubParasInfo pubParasInfo)
        {
            string strSql = this.SQL_SELECT_ORDERS_WAVE_TEMPLATE.Replace("@_@", whLoginID);
            if (!(string.IsNullOrEmpty(MaxOrders) || !(MaxOrders != "0")))
            {
                strSql = strSql.Replace("@MAX_ORDERS", "top " + MaxOrders);
            }
            else
            {
                strSql = strSql.Replace("@MAX_ORDERS", "");
            }
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    strSql = strSql + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    strSql = strSql + " AND " + sqlWhere[i].ToString();
                }
            }
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<OrdersInfo> ordersList = new List<OrdersInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, strSql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00003;
                    OrdersInfo ordersInfo = new OrdersInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), (reader["ORDER_DATE"].ToString() != "") ? DateTime.Parse(reader["ORDER_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["REQUESTED_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["REQUESTED_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["ACTUAL_SHIP_DATE"].ToString() != "") ? DateTime.Parse(reader["ACTUAL_SHIP_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["DELIVERY_DATE2"].ToString() != "") ? DateTime.Parse(reader["DELIVERY_DATE2"].ToString()) : (CS00003 = new DateTime()), reader["PRIORITY"].ToString(), reader["TYPE"].ToString(), reader["ORDER_GROUP"].ToString(), (reader["OPEN_QTY"].ToString() != "") ? decimal.Parse(reader["OPEN_QTY"].ToString()) : 0M, reader["STATUS"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["EXTERNAL_ORDER_ID2"].ToString(), reader["REFERENCE_NUM"].ToString(), reader["TRADING_PARTNER"].ToString(), reader["RECEIPT_ID"].ToString(), reader["CONSIGNEE_ID"].ToString(), reader["C_COMPANY"].ToString(), reader["C_ADDRESS"].ToString(), reader["C_CITY"].ToString(), reader["C_PROV"].ToString(), reader["C_ZIP"].ToString(), reader["C_COUNTRY_CODE"].ToString(), reader["C_COUNTRY"].ToString(), reader["C_VAT"].ToString(), reader["C_CONTACT1"].ToString(), reader["C_CONTACT2"].ToString(), reader["C_EMAIL1"].ToString(), reader["C_EMAIL2"].ToString(), reader["C_PHONE1"].ToString(), reader["C_PHONE2"].ToString(), reader["C_FAX1"].ToString(), reader["C_FAX2"].ToString(), reader["BILL_TO_ID"].ToString(), reader["B_COMPANY"].ToString(), reader["B_ADDRESS"].ToString(), reader["B_CITY"].ToString(), reader["B_STATE"].ToString(), reader["B_ZIP"].ToString(), reader["B_COUNTRY_CODE"].ToString(), reader["B_COUNTRY"].ToString(), reader["B_CONTACT1"].ToString(), reader["B_CONTACT2"].ToString(), reader["B_EMAIL1"].ToString(), reader["B_EMAIL2"].ToString(), reader["B_PHONE1"].ToString(), reader["B_PHONE2"].ToString(), reader["B_FAX1"].ToString(), reader["B_FAX2"].ToString(), reader["B_VAT"].ToString(), reader["CARRIER_ID"].ToString(), reader["CARRIER_NAME"].ToString(), reader["CARRIER_ADDRESS"].ToString(), reader["CARRIER_CITY"].ToString(), reader["CARRIER_PROV"].ToString(), reader["CARRIER_ZIP"].ToString(), reader["CARRIER_COUNTRY_CODE"].ToString(), reader["CARRIER_COUNTRY"].ToString(), reader["CARRIER_PHONE"].ToString(), (reader["DEPARTURE_DATE"].ToString() != "") ? DateTime.Parse(reader["DEPARTURE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["TRAILER_NUMBER"].ToString(), reader["TRAILER_OWNER"].ToString(), reader["DRIVER_NAME"].ToString(), reader["PRO_NUMBER"].ToString(), reader["DOOR"].ToString(), reader["ROUTE"].ToString(), reader["STOP"].ToString(), reader["STAGE"].ToString(), reader["TRANSPORTATION_MODE"].ToString(), reader["TRANSPORTATION_SERVICE"].ToString(), reader["INCO_TERMS"].ToString(), reader["PAYMENT_TERM"].ToString(), reader["CONTAINER_TYPE"].ToString(), (reader["CONTAINER_QTY"].ToString() != "") ? decimal.Parse(reader["CONTAINER_QTY"].ToString()) : 0M, reader["LOAD_ID"].ToString(), reader["EXTERNAL_LOAD_ID"].ToString(), reader["COUNTRY_OF_ORIGIN"].ToString(), reader["COUNTRY_DESTINATION"].ToString(), reader["DISCHARGE_PLACE"].ToString(), reader["DELIVERY_PLACE"].ToString(), reader["HANDLING_TYPE"].ToString(), reader["BATCH_FLAG"].ToString(), reader["SHIP_TOGETHER"].ToString(), reader["SORTATION_LOCATION"].ToString(), reader["ORDER_BREAK"].ToString(), reader["ENABLE_PACKING"].ToString(), reader["PACKING_LOCATION"].ToString(), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["BUYER_PO"].ToString(), (reader["ORDER_VALUE"].ToString() != "") ? decimal.Parse(reader["ORDER_VALUE"].ToString()) : 0M, reader["INTRANSIT_ID"].ToString(), reader["UPDATE_SOURCE"].ToString(), reader["SPLIT_ORDERS"].ToString(), reader["APPT_STATUS"].ToString(), reader["REMARK"].ToString(), reader["REMARK2"].ToString(), reader["IS_ACTIVE"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["DEST_WH_DB_ID"].ToString(), reader["STATUS_D"].ToString(), reader["TYPE_D"].ToString(), reader["CORE_RELEASE_ID"].ToString(), reader["IS_DECLARATION"].ToString(), reader["C_COUNTIES"].ToString(), reader["SHIP_NAME"].ToString(), reader["VOYAGE"].ToString(), reader["DESTINATION_PORT"].ToString(), reader["DEPARTURE_PORT"].ToString(), reader["TRANSSHIPMENT_PORT"].ToString(), reader["B_COUNTIES"].ToString(), reader["B_STREET"].ToString(), reader["C_STREET"].ToString(), reader["IS_COD"].ToString(), reader["IS_INVOICE_PRINT"].ToString(), reader["OPERATOR"].ToString(), (reader["OPERATING_DATE"].ToString() != "") ? DateTime.Parse(reader["OPERATING_DATE"].ToString()) : (CS00003 = new DateTime()), reader["ABNORMAL"].ToString(), reader["HANDLING"].ToString(), reader["DECLARATIONS_TYPE"].ToString(), reader["WORK_ORDER"].ToString(), reader["IS_PALLET_AUDIT"].ToString());
                    ordersList.Add(ordersInfo);
                }
            }
            return ordersList;
        }

        public DataSet GetOrderStatusByOrderDetailStatus(string orderID, string whLoginID)
        {
            string sql = this.SQL_GET_ORDER_DETAIL_STATUS_FOR_ORDER_STATUS.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetOrderStatusByOrderDetailStatus(DataBase dataBase, DbTransaction tran, string orderID, string whLoginID)
        {
            string sql = this.SQL_GET_ORDER_DETAIL_STATUS_FOR_ORDER_STATUS.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public OrdersInfo GetShippingReviewByOrderID(string orderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_SHIPPING_REVIEW_BY_ORDER_ID.Replace("@_@", whLoginID) + " WHERE O.ORDER_ID = @ORDER_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            OrdersInfo ordersInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    ordersInfo = new OrdersInfo(reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["STATUS"].ToString(), reader["STATUS_D"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["REFERENCE_NUM"].ToString(), (reader["TOTALE_QTY"].ToString() != "") ? decimal.Parse(reader["TOTALE_QTY"].ToString()) : 0M, (reader["SKU_GROUP_COUNT"].ToString() != "") ? int.Parse(reader["SKU_GROUP_COUNT"].ToString()) : 0);
                }
            }
            return ordersInfo;
        }

        public OrdersInfo GetShippingWeightByOrderID(string orderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_SHIPPING_WHIGHT_BY_ORDER_ID.Replace("@_@", whLoginID) + " WHERE O.ORDER_ID = @ORDER_ID " + "  GROUP BY O.ORDER_ID,O.OWNER_ID,O.STATUS,T.[TEXT],O.CARRIER_ID,O.WAYBILL_NO,O.ACTUAL_WEIGHT , O.PRINT_COUNT,O.REFERENCE_NUM,O.IS_CANCEL ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            OrdersInfo ordersInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    ordersInfo = new OrdersInfo(reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["STATUS"].ToString(), reader["STATUS_D"].ToString(), reader["CARRIER_ID"].ToString(), reader["WAYBILL_NO"].ToString(), (reader["ACTUAL_WEIGHT"].ToString() != "") ? decimal.Parse(reader["ACTUAL_WEIGHT"].ToString()) : 0M, (reader["PRINT_COUNT"].ToString() != "") ? int.Parse(reader["PRINT_COUNT"].ToString()) : 0, reader["IS_PRINT"].ToString(), reader["REFERENCE_NUM"].ToString(), (reader["TOTAL_WGT"].ToString() != "") ? decimal.Parse(reader["TOTAL_WGT"].ToString()) : 0M, reader["IS_CANCEL"].ToString());
                }
            }
            return ordersInfo;
        }

        public DataSet GetUnitPriceAndPoIDByLot4Shipping(string lot, string whLoginID)
        {
            string sql = this.SQL_GET_PO_PRICE_FOR_SHIPPING.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lot) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetUnitPriceAndPoIDByLot4Shipping(DataBase dataBase, DbTransaction tran, string lot, string whLoginID)
        {
            string sql = this.SQL_GET_PO_PRICE_FOR_SHIPPING.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOT", lot) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public int InsertOrders(OrdersInfo ordersInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                ordersInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_Orders_Parameters(ordersInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ORDERS.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertOrders(DataBase dataBase, DbTransaction tran, OrdersInfo ordersInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                ordersInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_Orders_Parameters(ordersInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_ORDERS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_Orders_Parameters(OrdersInfo ordersInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.ORDER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.REQUESTED_SHIP_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.ACTUAL_SHIP_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.DELIVERY_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.DELIVERY_DATE2, SqlDbType.DateTime, 8), new SqlParameter(this.PRIORITY, SqlDbType.VarChar, 20), new SqlParameter(this.TYPE, SqlDbType.VarChar, 10), new SqlParameter(this.ORDER_GROUP, SqlDbType.VarChar, 20), new SqlParameter(this.OPEN_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.EXTERN_ORDER_ID, SqlDbType.VarChar, 50), new SqlParameter(this.EXTERNAL_ORDER_ID2, SqlDbType.VarChar, 50), new SqlParameter(this.REFERENCE_NUM, SqlDbType.VarChar, 30), 
                new SqlParameter(this.TRADING_PARTNER, SqlDbType.VarChar, 30), new SqlParameter(this.RECEIPT_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CONSIGNEE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.C_COMPANY, SqlDbType.VarChar, 50), new SqlParameter(this.C_ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.C_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.C_PROV, SqlDbType.VarChar, 20), new SqlParameter(this.C_ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.C_COUNTRY_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.C_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.C_VAT, SqlDbType.VarChar, 20), new SqlParameter(this.C_CONTACT1, SqlDbType.VarChar, 30), new SqlParameter(this.C_CONTACT2, SqlDbType.VarChar, 30), new SqlParameter(this.C_EMAIL1, SqlDbType.VarChar, 60), new SqlParameter(this.C_EMAIL2, SqlDbType.VarChar, 60), new SqlParameter(this.C_PHONE1, SqlDbType.VarChar, 20), 
                new SqlParameter(this.C_PHONE2, SqlDbType.VarChar, 20), new SqlParameter(this.C_FAX1, SqlDbType.VarChar, 20), new SqlParameter(this.C_FAX2, SqlDbType.VarChar, 20), new SqlParameter(this.BILL_TO_ID, SqlDbType.VarChar, 20), new SqlParameter(this.B_COMPANY, SqlDbType.VarChar, 20), new SqlParameter(this.B_ADDRESS, SqlDbType.VarChar, 200), new SqlParameter(this.B_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.B_STATE, SqlDbType.VarChar, 20), new SqlParameter(this.B_ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.B_COUNTRY_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.B_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.B_CONTACT1, SqlDbType.VarChar, 30), new SqlParameter(this.B_CONTACT2, SqlDbType.VarChar, 30), new SqlParameter(this.B_EMAIL1, SqlDbType.VarChar, 60), new SqlParameter(this.B_EMAIL2, SqlDbType.VarChar, 60), new SqlParameter(this.B_PHONE1, SqlDbType.VarChar, 20), 
                new SqlParameter(this.B_PHONE2, SqlDbType.VarChar, 20), new SqlParameter(this.B_FAX1, SqlDbType.VarChar, 20), new SqlParameter(this.B_FAX2, SqlDbType.VarChar, 20), new SqlParameter(this.B_VAT, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_ID, SqlDbType.VarChar, 50), new SqlParameter(this.CARRIER_NAME, SqlDbType.VarChar, 50), new SqlParameter(this.CARRIER_ADDRESS, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_CITY, SqlDbType.VarChar, 50), new SqlParameter(this.CARRIER_PROV, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_ZIP, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_COUNTRY_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.CARRIER_COUNTRY, SqlDbType.VarChar, 30), new SqlParameter(this.CARRIER_PHONE, SqlDbType.VarChar, 20), new SqlParameter(this.DEPARTURE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.TRAILER_NUMBER, SqlDbType.VarChar, 0x19), new SqlParameter(this.TRAILER_OWNER, SqlDbType.VarChar, 0x19), 
                new SqlParameter(this.DRIVER_NAME, SqlDbType.VarChar, 0x19), new SqlParameter(this.PRO_NUMBER, SqlDbType.VarChar, 0x20), new SqlParameter(this.DOOR, SqlDbType.VarChar, 20), new SqlParameter(this.ROUTE, SqlDbType.VarChar, 10), new SqlParameter(this.STOP, SqlDbType.VarChar, 10), new SqlParameter(this.STAGE, SqlDbType.VarChar, 10), new SqlParameter(this.TRANSPORTATION_MODE, SqlDbType.VarChar, 30), new SqlParameter(this.TRANSPORTATION_SERVICE, SqlDbType.VarChar, 30), new SqlParameter(this.INCO_TERMS, SqlDbType.VarChar, 20), new SqlParameter(this.PAYMENT_TERM, SqlDbType.VarChar, 20), new SqlParameter(this.CONTAINER_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.CONTAINER_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.LOAD_ID, SqlDbType.VarChar, 20), new SqlParameter(this.EXTERNAL_LOAD_ID, SqlDbType.VarChar, 20), new SqlParameter(this.COUNTRY_OF_ORIGIN, SqlDbType.VarChar, 30), new SqlParameter(this.COUNTRY_DESTINATION, SqlDbType.VarChar, 30), 
                new SqlParameter(this.DISCHARGE_PLACE, SqlDbType.VarChar, 30), new SqlParameter(this.DELIVERY_PLACE, SqlDbType.VarChar, 30), new SqlParameter(this.HANDLING_TYPE, SqlDbType.VarChar, 10), new SqlParameter(this.BATCH_FLAG, SqlDbType.VarChar, 1), new SqlParameter(this.SHIP_TOGETHER, SqlDbType.VarChar, 1), new SqlParameter(this.SORTATION_LOCATION, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_BREAK, SqlDbType.VarChar, 1), new SqlParameter(this.ENABLE_PACKING, SqlDbType.VarChar, 1), new SqlParameter(this.PACKING_LOCATION, SqlDbType.VarChar, 10), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), 
                new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.BUYER_PO, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_VALUE, SqlDbType.Decimal, 9), new SqlParameter(this.INTRANSIT_ID, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATE_SOURCE, SqlDbType.VarChar, 20), new SqlParameter(this.SPLIT_ORDERS, SqlDbType.VarChar, 1), new SqlParameter(this.APPT_STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x200), new SqlParameter(this.REMARK2, SqlDbType.VarChar, 0x200), new SqlParameter(this.IS_ACTIVE, SqlDbType.VarChar, 1), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.CORE_RELEASE_ID, SqlDbType.VarChar, 30), new SqlParameter(this.IS_DECLARATION, SqlDbType.VarChar, 1), 
                new SqlParameter(this.PACKING_TYPE, SqlDbType.VarChar, 50), new SqlParameter(this.WAYBILL_NO, SqlDbType.VarChar, 100), new SqlParameter(this.ACTUAL_WEIGHT, SqlDbType.Decimal, 0x16), new SqlParameter(this.C_COUNTIES, SqlDbType.VarChar, 50), new SqlParameter(this.SHIP_NAME, SqlDbType.VarChar, 50), new SqlParameter(this.VOYAGE, SqlDbType.VarChar, 50), new SqlParameter(this.DESTINATION_PORT, SqlDbType.VarChar, 50), new SqlParameter(this.DEPARTURE_PORT, SqlDbType.VarChar, 50), new SqlParameter(this.B_COUNTIES, SqlDbType.VarChar, 50), new SqlParameter(this.B_STREET, SqlDbType.VarChar, 50), new SqlParameter(this.C_STREET, SqlDbType.VarChar, 50), new SqlParameter(this.IS_COD, SqlDbType.VarChar, 1), new SqlParameter(this.IS_INVOICE_PRINT, SqlDbType.VarChar, 1), new SqlParameter(this.OPERATOR, SqlDbType.VarChar, 20), new SqlParameter(this.OPERATING_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.ABNORMAL, SqlDbType.VarChar, 50), 
                new SqlParameter(this.HANDLING, SqlDbType.VarChar, 50), new SqlParameter(this.DECLARATIONS_TYPE, SqlDbType.VarChar, 10), new SqlParameter(this.WORK_ORDER, SqlDbType.VarChar, 30), new SqlParameter(this.TRANSSHIPMENT_PORT, SqlDbType.VarChar, 50)
             };
            if (!string.IsNullOrEmpty(ordersInfo.WhID))
            {
                paramArray[0].Value = ordersInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = ordersInfo.OrderID;
            paramArray[2].Value = ordersInfo.OwnerID;
            paramArray[3].Value = ordersInfo.OrderDate;
            DateTime CS00003 = new DateTime();
            if (ordersInfo.RequestedShipDate != CS00003)
            {
                paramArray[4].Value = ordersInfo.RequestedShipDate;
            }
            else
            {
                paramArray[4].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (ordersInfo.ActualShipDate != CS00003)
            {
                paramArray[5].Value = ordersInfo.ActualShipDate;
            }
            else
            {
                paramArray[5].Value = DBNull.Value;
            }
            paramArray[6].Value = ordersInfo.DeliveryDate;
            CS00003 = new DateTime();
            if (ordersInfo.DeliveryDate2 != CS00003)
            {
                paramArray[7].Value = ordersInfo.DeliveryDate2;
            }
            else
            {
                paramArray[7].Value = DBNull.Value;
            }
            paramArray[8].Value = ordersInfo.Priority;
            paramArray[9].Value = ordersInfo.Type;
            paramArray[10].Value = ordersInfo.OrderGroup;
            paramArray[11].Value = ordersInfo.OpenQty;
            paramArray[12].Value = ordersInfo.Status;
            paramArray[13].Value = ordersInfo.ExternOrderID;
            if (!string.IsNullOrEmpty(ordersInfo.ExternalOrderId2))
            {
                paramArray[14].Value = ordersInfo.ExternalOrderId2;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.ReferenceNum))
            {
                paramArray[15].Value = ordersInfo.ReferenceNum;
            }
            else
            {
                paramArray[15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.TradingPartner))
            {
                paramArray[0x10].Value = ordersInfo.TradingPartner;
            }
            else
            {
                paramArray[0x10].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.ReceiptID))
            {
                paramArray[0x11].Value = ordersInfo.ReceiptID;
            }
            else
            {
                paramArray[0x11].Value = DBNull.Value;
            }
            paramArray[0x12].Value = ordersInfo.ConsigneeID;
            if (!string.IsNullOrEmpty(ordersInfo.CCompany))
            {
                paramArray[0x13].Value = ordersInfo.CCompany;
            }
            else
            {
                paramArray[0x13].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CAddress))
            {
                paramArray[20].Value = ordersInfo.CAddress;
            }
            else
            {
                paramArray[20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CCity))
            {
                paramArray[0x15].Value = ordersInfo.CCity;
            }
            else
            {
                paramArray[0x15].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CProv))
            {
                paramArray[0x16].Value = ordersInfo.CProv;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CZip))
            {
                paramArray[0x17].Value = ordersInfo.CZip;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CCountryCode))
            {
                paramArray[0x18].Value = ordersInfo.CCountryCode;
            }
            else
            {
                paramArray[0x18].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CCountry))
            {
                paramArray[0x19].Value = ordersInfo.CCountry;
            }
            else
            {
                paramArray[0x19].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CVat))
            {
                paramArray[0x1a].Value = ordersInfo.CVat;
            }
            else
            {
                paramArray[0x1a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CContact1))
            {
                paramArray[0x1b].Value = ordersInfo.CContact1;
            }
            else
            {
                paramArray[0x1b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CContact2))
            {
                paramArray[0x1c].Value = ordersInfo.CContact2;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CEmail1))
            {
                paramArray[0x1d].Value = ordersInfo.CEmail1;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CEmail2))
            {
                paramArray[30].Value = ordersInfo.CEmail2;
            }
            else
            {
                paramArray[30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CPhone1))
            {
                paramArray[0x1f].Value = ordersInfo.CPhone1;
            }
            else
            {
                paramArray[0x1f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CPhone2))
            {
                paramArray[0x20].Value = ordersInfo.CPhone2;
            }
            else
            {
                paramArray[0x20].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CFax1))
            {
                paramArray[0x21].Value = ordersInfo.CFax1;
            }
            else
            {
                paramArray[0x21].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CFax2))
            {
                paramArray[0x22].Value = ordersInfo.CFax2;
            }
            else
            {
                paramArray[0x22].Value = DBNull.Value;
            }
            paramArray[0x23].Value = ordersInfo.BillToID;
            if (!string.IsNullOrEmpty(ordersInfo.BCompany))
            {
                paramArray[0x24].Value = ordersInfo.BCompany;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BAddress))
            {
                paramArray[0x25].Value = ordersInfo.BAddress;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BCity))
            {
                paramArray[0x26].Value = ordersInfo.BCity;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BState))
            {
                paramArray[0x27].Value = ordersInfo.BState;
            }
            else
            {
                paramArray[0x27].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BZip))
            {
                paramArray[40].Value = ordersInfo.BZip;
            }
            else
            {
                paramArray[40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BCountryCode))
            {
                paramArray[0x29].Value = ordersInfo.BCountryCode;
            }
            else
            {
                paramArray[0x29].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BCountry))
            {
                paramArray[0x2a].Value = ordersInfo.BCountry;
            }
            else
            {
                paramArray[0x2a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BContact1))
            {
                paramArray[0x2b].Value = ordersInfo.BContact1;
            }
            else
            {
                paramArray[0x2b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BContact2))
            {
                paramArray[0x2c].Value = ordersInfo.BContact2;
            }
            else
            {
                paramArray[0x2c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BEmail1))
            {
                paramArray[0x2d].Value = ordersInfo.BEmail1;
            }
            else
            {
                paramArray[0x2d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BEmail2))
            {
                paramArray[0x2e].Value = ordersInfo.BEmail2;
            }
            else
            {
                paramArray[0x2e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BPhone1))
            {
                paramArray[0x2f].Value = ordersInfo.BPhone1;
            }
            else
            {
                paramArray[0x2f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BPhone2))
            {
                paramArray[0x30].Value = ordersInfo.BPhone2;
            }
            else
            {
                paramArray[0x30].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BFax1))
            {
                paramArray[0x31].Value = ordersInfo.BFax1;
            }
            else
            {
                paramArray[0x31].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BFax2))
            {
                paramArray[50].Value = ordersInfo.BFax2;
            }
            else
            {
                paramArray[50].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BVat))
            {
                paramArray[0x33].Value = ordersInfo.BVat;
            }
            else
            {
                paramArray[0x33].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CarrierID))
            {
                paramArray[0x34].Value = ordersInfo.CarrierID;
            }
            else
            {
                paramArray[0x34].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CarrierName))
            {
                paramArray[0x35].Value = ordersInfo.CarrierName;
            }
            else
            {
                paramArray[0x35].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CarrierAddress))
            {
                paramArray[0x36].Value = ordersInfo.CarrierAddress;
            }
            else
            {
                paramArray[0x36].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CarrierCity))
            {
                paramArray[0x37].Value = ordersInfo.CarrierCity;
            }
            else
            {
                paramArray[0x37].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CarrierProv))
            {
                paramArray[0x38].Value = ordersInfo.CarrierProv;
            }
            else
            {
                paramArray[0x38].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CarrierZip))
            {
                paramArray[0x39].Value = ordersInfo.CarrierZip;
            }
            else
            {
                paramArray[0x39].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CarrierCountryCode))
            {
                paramArray[0x3a].Value = ordersInfo.CarrierCountryCode;
            }
            else
            {
                paramArray[0x3a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CarrierCountry))
            {
                paramArray[0x3b].Value = ordersInfo.CarrierCountry;
            }
            else
            {
                paramArray[0x3b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CarrierPhone))
            {
                paramArray[60].Value = ordersInfo.CarrierPhone;
            }
            else
            {
                paramArray[60].Value = DBNull.Value;
            }
            paramArray[0x3d].Value = ordersInfo.DepartureDate;
            if (!string.IsNullOrEmpty(ordersInfo.TrailerNumber))
            {
                paramArray[0x3e].Value = ordersInfo.TrailerNumber;
            }
            else
            {
                paramArray[0x3e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.TrailerOwner))
            {
                paramArray[0x3f].Value = ordersInfo.TrailerOwner;
            }
            else
            {
                paramArray[0x3f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.DriverName))
            {
                paramArray[0x40].Value = ordersInfo.DriverName;
            }
            else
            {
                paramArray[0x40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.ProNumber))
            {
                paramArray[0x41].Value = ordersInfo.ProNumber;
            }
            else
            {
                paramArray[0x41].Value = DBNull.Value;
            }
            paramArray[0x42].Value = ordersInfo.Door;
            paramArray[0x43].Value = ordersInfo.Route;
            paramArray[0x44].Value = ordersInfo.Stop;
            if (!string.IsNullOrEmpty(ordersInfo.Stage))
            {
                paramArray[0x45].Value = ordersInfo.Stage;
            }
            else
            {
                paramArray[0x45].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.TransportationMode))
            {
                paramArray[70].Value = ordersInfo.TransportationMode;
            }
            else
            {
                paramArray[70].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.TransportationService))
            {
                paramArray[0x47].Value = ordersInfo.TransportationService;
            }
            else
            {
                paramArray[0x47].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.IncoTerms))
            {
                paramArray[0x48].Value = ordersInfo.IncoTerms;
            }
            else
            {
                paramArray[0x48].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.PaymentTerm))
            {
                paramArray[0x49].Value = ordersInfo.PaymentTerm;
            }
            else
            {
                paramArray[0x49].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.ContainerType))
            {
                paramArray[0x4a].Value = ordersInfo.ContainerType;
            }
            else
            {
                paramArray[0x4a].Value = DBNull.Value;
            }
            paramArray[0x4b].Value = ordersInfo.ContainerQty;
            if (!string.IsNullOrEmpty(ordersInfo.LoadID))
            {
                paramArray[0x4c].Value = ordersInfo.LoadID;
            }
            else
            {
                paramArray[0x4c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.ExternalLoadID))
            {
                paramArray[0x4d].Value = ordersInfo.ExternalLoadID;
            }
            else
            {
                paramArray[0x4d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CountryOfOrigin))
            {
                paramArray[0x4e].Value = ordersInfo.CountryOfOrigin;
            }
            else
            {
                paramArray[0x4e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CountryDestination))
            {
                paramArray[0x4f].Value = ordersInfo.CountryDestination;
            }
            else
            {
                paramArray[0x4f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.DischargePlace))
            {
                paramArray[80].Value = ordersInfo.DischargePlace;
            }
            else
            {
                paramArray[80].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.DeliveryPlace))
            {
                paramArray[0x51].Value = ordersInfo.DeliveryPlace;
            }
            else
            {
                paramArray[0x51].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.HandlingType))
            {
                paramArray[0x52].Value = ordersInfo.HandlingType;
            }
            else
            {
                paramArray[0x52].Value = DBNull.Value;
            }
            paramArray[0x53].Value = ordersInfo.BatchFlag;
            paramArray[0x54].Value = ordersInfo.ShipTogether;
            if (!string.IsNullOrEmpty(ordersInfo.SortationLocation))
            {
                paramArray[0x55].Value = ordersInfo.SortationLocation;
            }
            else
            {
                paramArray[0x55].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.OrderBreak))
            {
                paramArray[0x56].Value = ordersInfo.OrderBreak;
            }
            else
            {
                paramArray[0x56].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.EnablePacking))
            {
                paramArray[0x57].Value = ordersInfo.EnablePacking;
            }
            else
            {
                paramArray[0x57].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.PackingLocation))
            {
                paramArray[0x58].Value = ordersInfo.PackingLocation;
            }
            else
            {
                paramArray[0x58].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Udf1))
            {
                paramArray[0x59].Value = ordersInfo.Udf1;
            }
            else
            {
                paramArray[0x59].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Udf2))
            {
                paramArray[90].Value = ordersInfo.Udf2;
            }
            else
            {
                paramArray[90].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Udf3))
            {
                paramArray[0x5b].Value = ordersInfo.Udf3;
            }
            else
            {
                paramArray[0x5b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Udf4))
            {
                paramArray[0x5c].Value = ordersInfo.Udf4;
            }
            else
            {
                paramArray[0x5c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Udf5))
            {
                paramArray[0x5d].Value = ordersInfo.Udf5;
            }
            else
            {
                paramArray[0x5d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Udf6))
            {
                paramArray[0x5e].Value = ordersInfo.Udf6;
            }
            else
            {
                paramArray[0x5e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Udf7))
            {
                paramArray[0x5f].Value = ordersInfo.Udf7;
            }
            else
            {
                paramArray[0x5f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Udf8))
            {
                paramArray[0x60].Value = ordersInfo.Udf8;
            }
            else
            {
                paramArray[0x60].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Udf9))
            {
                paramArray[0x61].Value = ordersInfo.Udf9;
            }
            else
            {
                paramArray[0x61].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Udf10))
            {
                paramArray[0x62].Value = ordersInfo.Udf10;
            }
            else
            {
                paramArray[0x62].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BuyerPo))
            {
                paramArray[0x63].Value = ordersInfo.BuyerPo;
            }
            else
            {
                paramArray[0x63].Value = DBNull.Value;
            }
            paramArray[100].Value = ordersInfo.OrderValue;
            if (!string.IsNullOrEmpty(ordersInfo.IntransitID))
            {
                paramArray[0x65].Value = ordersInfo.IntransitID;
            }
            else
            {
                paramArray[0x65].Value = DBNull.Value;
            }
            paramArray[0x66].Value = ordersInfo.UpdateSource;
            if (!string.IsNullOrEmpty(ordersInfo.SplitOrders))
            {
                paramArray[0x67].Value = ordersInfo.SplitOrders;
            }
            else
            {
                paramArray[0x67].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.ApptStatus))
            {
                paramArray[0x68].Value = ordersInfo.ApptStatus;
            }
            else
            {
                paramArray[0x68].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Remark))
            {
                paramArray[0x69].Value = ordersInfo.Remark;
            }
            else
            {
                paramArray[0x69].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Remark2))
            {
                paramArray[0x6a].Value = ordersInfo.Remark2;
            }
            else
            {
                paramArray[0x6a].Value = DBNull.Value;
            }
            paramArray[0x6b].Value = ordersInfo.IsActive;
            paramArray[0x6c].Value = ordersInfo.CreatedBy;
            paramArray[0x6d].Value = ordersInfo.UpdatedBy;
            if (!string.IsNullOrEmpty(ordersInfo.CoreReleaseID))
            {
                paramArray[110].Value = ordersInfo.CoreReleaseID;
            }
            else
            {
                paramArray[110].Value = "";
            }
            if (!string.IsNullOrEmpty(ordersInfo.IsDeclartion))
            {
                paramArray[0x6f].Value = ordersInfo.IsDeclartion;
            }
            else
            {
                paramArray[0x6f].Value = "N";
            }
            if (!string.IsNullOrEmpty(ordersInfo.PackingType))
            {
                paramArray[0x70].Value = ordersInfo.PackingType;
            }
            else
            {
                paramArray[0x70].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.WaybillNo))
            {
                paramArray[0x71].Value = ordersInfo.WaybillNo;
            }
            else
            {
                paramArray[0x71].Value = DBNull.Value;
            }
            paramArray[0x72].Value = ordersInfo.ActualWeight;
            if (!string.IsNullOrEmpty(ordersInfo.CCounties))
            {
                paramArray[0x73].Value = ordersInfo.CCounties;
            }
            else
            {
                paramArray[0x73].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.ShipName))
            {
                paramArray[0x74].Value = ordersInfo.ShipName;
            }
            else
            {
                paramArray[0x74].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Voyage))
            {
                paramArray[0x75].Value = ordersInfo.Voyage;
            }
            else
            {
                paramArray[0x75].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.DestinationPort))
            {
                paramArray[0x76].Value = ordersInfo.DestinationPort;
            }
            else
            {
                paramArray[0x76].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.DeparturePort))
            {
                paramArray[0x77].Value = ordersInfo.DeparturePort;
            }
            else
            {
                paramArray[0x77].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BCounties))
            {
                paramArray[120].Value = ordersInfo.BCounties;
            }
            else
            {
                paramArray[120].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.BStreet))
            {
                paramArray[0x79].Value = ordersInfo.BStreet;
            }
            else
            {
                paramArray[0x79].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.CStreet))
            {
                paramArray[0x7a].Value = ordersInfo.CStreet;
            }
            else
            {
                paramArray[0x7a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.IsCod))
            {
                paramArray[0x7b].Value = ordersInfo.IsCod;
            }
            else
            {
                paramArray[0x7b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.IsInvoicePrint))
            {
                paramArray[0x7c].Value = ordersInfo.IsInvoicePrint;
            }
            else
            {
                paramArray[0x7c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Operator))
            {
                paramArray[0x7d].Value = ordersInfo.Operator;
            }
            else
            {
                paramArray[0x7d].Value = DBNull.Value;
            }
            if (ordersInfo.OperatingDate != new DateTime())
            {
                paramArray[0x7e].Value = ordersInfo.OperatingDate;
            }
            else
            {
                paramArray[0x7e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Abnormal))
            {
                paramArray[0x7f].Value = ordersInfo.Abnormal;
            }
            else
            {
                paramArray[0x7f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.Handling))
            {
                paramArray[0x80].Value = ordersInfo.Handling;
            }
            else
            {
                paramArray[0x80].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.DeclarationsType))
            {
                paramArray[0x81].Value = ordersInfo.DeclarationsType;
            }
            else
            {
                paramArray[0x81].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.WorkOrder))
            {
                paramArray[130].Value = ordersInfo.WorkOrder;
            }
            else
            {
                paramArray[130].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(ordersInfo.TransshipmentPort))
            {
                paramArray[0x83].Value = ordersInfo.TransshipmentPort;
                return paramArray;
            }
            paramArray[0x83].Value = DBNull.Value;
            return paramArray;
        }

        public int UpdateExternalOrderIDByDIDcr(string declarationsID, string coreReleaseID, string whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            List<string> orderIDList = this.GetOrderIDByCoreReleaseID(coreReleaseID, whLoginID);
            string sql = "UPDATE @_@ORDERS SET EXTERN_ORDER_ID = ISNULL(EXTERN_ORDER_ID,'') + (CASE WHEN ISNULL(EXTERN_ORDER_ID,'') !=''  THEN   ','+@DECLARATIONSID ELSE   @DECLARATIONSID END), UPDATED_BY = @UPDATED_BY , UPDATED_DATE = GETDATE() WHERE ORDER_ID = @ORDER_ID AND ( STATUS = 'Closed' OR STATUS = 'Shipped' ) ";
            try
            {
                for (int i = 0; i < orderIDList.Count; i++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@DECLARATIONSID", declarationsID), new SqlParameter("@ORDER_ID", orderIDList[i]), new SqlParameter("@UPDATED_BY", pubParasInfo.UserID) };
                    result += db.ExecuteNonQuery(tran, CommandType.Text, sql.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateOrderActualShipDateByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string ActualShipDate, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@UPDATED_DATE", ActualShipDate), new SqlParameter("@ACTUAL_SHIP_DATE", ActualShipDate), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ACTUAL_SHIP_DATE_BY_ORDER_ID.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderBatchFlag(string batchFlag, string orderID, string whLoginID)
        {
            string sql = this.SQL_UPDATE_ORDER_BATCH_FLAG.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BATCH_FLAG", batchFlag), new SqlParameter("@ORDER_ID", orderID) };
            return DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
        }

        public int UpdateOrderBatchFlag(DataBase dataBase, DbTransaction tran, string batchFlag, string orderID, string whLoginID)
        {
            string sql = this.SQL_UPDATE_ORDER_BATCH_FLAG.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@BATCH_FLAG", batchFlag), new SqlParameter("@ORDER_ID", orderID) };
            return dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
        }

        public int UpdateOrderCarrierIDByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string CarrierID, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@CARRIER_ID", CarrierID), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@ORDER_ID", orderID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_CARRIER_ID_BY_ORDER_ID.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrders(OrdersInfo ordersInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                ordersInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_Orders_Parameters(ordersInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ORDERS.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateOrders(DataBase dataBase, DbTransaction tran, OrdersInfo ordersInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                ordersInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_Orders_Parameters(ordersInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ORDERS.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrdersByShippingWeight(OrdersInfo orderinfo, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_ORDERS_SHIPPING_WEIGHT;
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PACKING_TYPE", orderinfo.PackingType), new SqlParameter("@WAYBILL_NO", orderinfo.WaybillNo), new SqlParameter("@ACTUAL_WEIGHT", orderinfo.ActualWeight), new SqlParameter("@UPDATED_BY", orderinfo.UpdatedBy), new SqlParameter("@ORDER_ID", orderinfo.OrderID) };
                if (paras != null)
                {
                    result += DBHelper.ExecuteNonQuery(CommandType.Text, sql.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrdersExternOrderIDByOrderID(string orderID, string ExternOrderID, string whLoginID, PubParasInfo pubParasInfo)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_ORDER_EXTERN_ORDER_ID_BY_ORDER_ID;
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@EXTERN_ORDER_ID", ExternOrderID), new SqlParameter("@UPDATED_BY", pubParasInfo.UserID), new SqlParameter("@ORDER_ID", orderID) };
                if (paras != null)
                {
                    result += DBHelper.ExecuteNonQuery(CommandType.Text, sql.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrdersIsCancel(string orderid, string isCancel, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = "UPDATE @_@ORDERS SET IS_CANCEL=@IS_CANCEL,UPDATED_BY=@UPDATED_BY,UPDATED_DATE=GETDATE() WHERE ORDER_ID=@ORDER_ID";
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderid), new SqlParameter("@IS_CANCEL", isCancel), new SqlParameter("@UPDATED_BY", pubParasInfo.UserID) };
                if (paras != null)
                {
                    result += DBHelper.ExecuteNonQuery(CommandType.Text, sql.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrdersPackingType(string orderID, string packingType, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = "UPDATE @_@ORDERS SET PACKING_TYPE=@PACKING_TYPE,UPDATED_BY=@UPDATED_BY,UPDATED_DATE=GETDATE() WHERE ORDER_ID=@ORDER_ID";
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@PACKING_TYPE", packingType), new SqlParameter("@UPDATED_BY", pubParasInfo.UserID) };
                if (paras != null)
                {
                    result += DBHelper.ExecuteNonQuery(CommandType.Text, sql.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrdersStatus(string orderID, string ordersStatus, string udpatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_ORDERS_STATUS;
                if ((ordersStatus == OrderStatus.PartShipped) || (ordersStatus == OrderStatus.Shipped))
                {
                    sql = this.SQL_UPDATE_ORDERS_STATUS_4_SHIP;
                }
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", ordersStatus), new SqlParameter("@UPDATED_BY", udpatedBy), new SqlParameter("@ORDER_ID", orderID) };
                if (paras != null)
                {
                    result += DBHelper.ExecuteNonQuery(CommandType.Text, sql.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrdersStatus(DataBase dataBase, DbTransaction tran, string orderID, string ordersStatus, string udpatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_ORDERS_STATUS;
                if ((ordersStatus == OrderStatus.PartShipped) || (ordersStatus == OrderStatus.Shipped))
                {
                    sql = this.SQL_UPDATE_ORDERS_STATUS_4_SHIP;
                }
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", ordersStatus), new SqlParameter("@UPDATED_BY", udpatedBy), new SqlParameter("@ORDER_ID", orderID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateOrderTracking(DataBase dataBase, DbTransaction tran, string orderID, string ordersStatus, string operators, DateTime operatingDate, string abnormal, string handling, string udpatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OPERATOR", operators), new SqlParameter("@OPERATING_DATE", operatingDate), new SqlParameter("@ABNORMAL", abnormal), new SqlParameter("@HANDLING", handling), new SqlParameter("@STATUS", ordersStatus), new SqlParameter("@UPDATED_BY", udpatedBy), new SqlParameter("@ORDER_ID", orderID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_ORDER_TRACKING_BY_ORDER_ID.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

